Solved

Delete specific rows with VBA

Posted on 2014-11-07
9
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 52

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 52

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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 52

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 52

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

617 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