condensing this VBA

Jon von der Heyden

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,912
Office Version
  1. 365
Platform
  1. Windows
Hi,

I suspect this code is more complicated than it needs to be. I have a series of loops doing different actions on the same column. Is there any way of condensing this?

Code: 
'Delete blank rows from Col G

For i = lastrow To 6 Step -1
    Set x = Range("G" & i)
    
    If x.Value = "" Then
        x.EntireRow.Delete shift:=xlUp
    End If
Next i
'---------------------------------------------------------------------------

'Delete Current Period Labels from Col G

For i = lastrow To 6 Step -1
    Set x = Range("G" & i)
    
    If x.Value = "Current Period" Then
        x.EntireRow.Delete shift:=xlUp
    End If
Next i
'----------------------------------------------------------------------------

'Delete Amount Labels from Col G

For i = lastrow To 6 Step -1
    Set x = Range("G" & i)
    
    If x.Value = "Amount £" Then
        x.EntireRow.Delete shift:=xlUp
    End If
Next i
'----------------------------------------------------------------------------

'Delete Date Labels from Col G

For i = lastrow To 6 Step -1
    Set x = Range("G" & i)
    
    If x.Value = Range("G5").Value Then
        x.EntireRow.Delete shift:=xlUp
    End If
Next i

Thanks,
Jon
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Jon

How about this:

Code: 
Sub test()

Dim rng As Range, rng2 As Range, lastrow As Long, crit As String


Set rng = Range("g6:g" & lastrow)
Set rng2 = Range("g5:g" & lastrow)
crit = Range("g5").Value

With rng2
    .AutoFilter field:=1, Criteria1:=""
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter field:=1, Criteria1:="Current Period"
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter field:=1, Criteria1:="Amount £"
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter field:=1, Criteria1:=crit
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Range("g5").AutoFilter

End Sub

This is not fully functional code - you need to define lastrow and also may need to amend the type of crit, but otherwise it should be very quick.

Best regards

Richard
 
Upvote 0
I saw this thread nominated for MrExcel's, Excel Gurus Gone Wild and I have a question...
Could this be used with a range of criteria?
What I'm saying is I have a list of products on Sheet(1) (we'll call list A) of my Workbook. I want to use that list to copy (opposed to delete) records in the range on "Products" worksheet which contain items in List A in columns M, N, O, P.

I want to copy these to a third worksheet.

Could anyone assist?

-- g
Thank you
 
Upvote 0
So your list of products, is this in a single column or multiple (which col(s) refer please)?

This should be achievable by passing an array of values to autofilter (since you are using 2010). If you answer q's above I'll have a stab at it for you. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Trouble with macro to delete rows
2
Replies
14
Views
324
Jeffrey Mahoney
Jeffrey Mahoney
K
  • Question Question
Help Needed
2
Replies
13
Views
353
kwavic
K
A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
660
jolivanes
jolivanes
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
666
mlan1604
M
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
167
Mazurofgod
M

Forum statistics

Threads
1,225,346
Messages
6,184,396
Members
453,229
Latest member
Piip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top