Solved

Delete specific rows with VBA

Posted on 2014-11-07
9
69 Views
Last Modified: 2014-11-12
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
Comment
Question by:Jagwarman
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40428037
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
 

Author Comment

by:Jagwarman
ID: 40428046
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
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40428123
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40428131
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
 

Author Comment

by:Jagwarman
ID: 40428132
Rgonzo you are brilliant many thanks have a great weekend
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40428147
@ 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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40428251
Do shapes not get deleted when they are linked to a cell / row?

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40428291
@robhenson Nope because it is a form Control but an ActiveX Control with Move and size with Cell would be deleted
0
 

Author Comment

by:Jagwarman
ID: 40428352
rgonzo I can't accept on this pc I will do it tonight from home
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question