[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Delete specific rows with VBA

Posted on 2014-11-07
9
Medium Priority
?
83 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 54

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 54

Accepted Solution

by:
Rgonzo1971 earned 2000 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 34

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 54

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 34

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 54

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

591 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