Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 4
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
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
0
 
JagwarmanAuthor Commented:
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
0
 
Rgonzo1971Commented:
then try

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("B" & lngRow & ":X" & lngRow).Delete Shift:=xlUp
End If

End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rob HensonIT & Database AssistantCommented:
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
0
 
JagwarmanAuthor Commented:
Rgonzo you are brilliant many thanks have a great weekend
0
 
Rgonzo1971Commented:
@ 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

0
 
Rob HensonIT & Database AssistantCommented:
Do shapes not get deleted when they are linked to a cell / row?

Thanks
Rob H
0
 
Rgonzo1971Commented:
@robhenson Nope because it is a form Control but an ActiveX Control with Move and size with Cell would be deleted
0
 
JagwarmanAuthor Commented:
rgonzo I can't accept on this pc I will do it tonight from home
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now