Change 'Entire Row delete' to 'cells in columns A-G'

[pcarmour]

Hi,

I have a macro that selects a variety of codes in column G and deletes the entire row when they are found.

I now just want to delete the cells in columns A-G and not the entire row but not sure what part of the code to change. Please see below.

Any help would be much appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)



Sub Macro1()
' Macro1 Macro
'Set rngDelete = Nothing
Set rngNew = Worksheets("Last Day Of Month").Range("G:G")
rngNew.Select
For Each aCell In Selection
Select Case aCell.Value
' Delete all these
Case "GAU", "JUST", "SIMI1", "SIMI2", "SIMI3", "SIMI4", "SIMI5", "SSC", "SSL", "SSZ", "TRO", "ZAILF", "HBXO", "WAS", "GGKXO", "BACCC"
If rngDelete Is Nothing Then
Set rngDelete = aCell
Else
Set rngDelete = Union(rngDelete, aCell)
End If
Case Else
'do nothing
End Select
Next aCell
rngDelete.EntireRow.Delete Shift:=xlShiftUp
Set rngNew = Nothing
Set aCell = Nothing
Set rngDelete = Nothing
End Sub

[pcarmour]

Sorry that doesn't look very helpful!

[Paul_Hossler]

I'd start with something like this.

When deleting, it's usually better to start from the bottom and delete your way to the top

Also, usually no need to .Select something



Option Explicit

Sub Macro1()
Dim iLastRow As Long, iRow As Long

With Worksheets("Last Day Of Month")
iLastRow = .Cells(.Rows.Count, 7).End(xlUp).Row

For iRow = iLastRow To 1 Step -1

Select Case .Cells(iRow, 7).Value
' Delete all these
Case "GAU", "JUST", "SIMI1", "SIMI2", "SIMI3", "SIMI4", "SIMI5", "SSC", "SSL", "SSZ", "TRO", "ZAILF", "HBXO", "WAS", "GGKXO", "BACCC"
.Cells(iRow, 1).Resize(1, 7).Delete Shift:=xlShiftUp
End Select
Next iRow
End With
End Sub



Paul

[pcarmour]

Hi Paul,
Thank you very much for your brilliant answer, VBA Express certainly has the experts to get your solutions from.

Thank you again.