Avatar of Jagwarman
Jagwarman
 asked on

Delete specific rows with VBA

Could an Expert provide me with VBA code that will delete Row[s] in Excel but only across specific columns from A to X

I also need the objects in those cells removed/deleted

I have attached a file which will hopefully make it clear what I need to do

I have put the before and after [but of course the after would not be on a different sheet] so I start with 5 rows and I want to remove Row 5 and leave 4 rows.

So looks like this to start

ABC
DEF
GHI
JKL
MNO

Remove row 5

ends up with

ABC
DEF
JKL
MNO
Remove.xlsx
Microsoft Excel

Avatar of undefined
Last Comment
Jagwarman

8/22/2022 - Mon
Rgonzo1971

Hi,

pls try

sub Macro() 

For Each shp In ActiveSheet.Shapes

If shp.TopLeftCell.Row = 5 Then
    shp.Delete
End If

Next
Range("B5:X5").Delete Shift:=xlUp

End Sub

Open in new window

Regards
Jagwarman

ASKER
Sorry I clearly did not explain fully.

In the example I wanted to remove row 5 but, there could be 50 rows or 100 rows and I might want to remove row 12 or 30 etc.

so I need a window to open to ask me what row do you want to remove, I enter a number and it is that row that is then removed.

Really sorry
ASKER CERTIFIED SOLUTION
Rgonzo1971

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

What is in the columns beyond column X?

If nothing, then you can just use AutoFilter to find the rows required to remove and then delete rows but this will remove the entire row.

Thanks
Rob H
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jagwarman

ASKER
Rgonzo you are brilliant many thanks have a great weekend
Rgonzo1971

@ Rob Henson

the problem lies with the checkboxes to be deleted

@jagwarman
Of course if you want the whole row to be deleted it would be

Sub Macro()
strRow = InputBox("Insert Row Number")

If IsNumeric(strRow) Then
    lngRow = CLng(strRow)
    For Each shp In ActiveSheet.Shapes
    
    If shp.TopLeftCell.Row = lngRow Then
        shp.Delete
    End If
    
    Next
    Range("A" & lngRow ).EntireRow.Delete Shift:=xlUp
End If

End Sub

Open in new window

Rob Henson

Do shapes not get deleted when they are linked to a cell / row?

Thanks
Rob H
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rgonzo1971

@robhenson Nope because it is a form Control but an ActiveX Control with Move and size with Cell would be deleted
Jagwarman

ASKER
rgonzo I can't accept on this pc I will do it tonight from home