pcarmour
01-13-2014, 07:17 AM
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
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