condensing this VBA

[Jon von der Heyden]

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?

'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

 

[Richard Schollar]

Jon

How about this:

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

 

[Jon von der Heyden]

I like - thanks!

 

[greegan]

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

 

[Jon von der Heyden]

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.