How to clear contents of a row when a number value is found, while not clearing formulas, using vba

[Corky4661]

Hello, I have information that is being taken from another workbook and being pasted into the main sheet using VBA. I would like to clear contents of all of the rows over to column G, based on a numerical value being found in column G. Column H and I have formulas. If the cell in column G is blank I would like to keep all of those rows. Any help you can offer would be awesome! Thank you in advance.

 

[Jamie McMillan]

Hello Corky,

The below code does what you were asking. You can add the code at the end of the 'copy and paste' macro - or just call EmptyRows at the end of it.

Sub EmptyRows()
Dim i As Integer 'i os each row number in turn
i = 1
For i = 1 To 20 'Loops through 20 rows (Change this to however many rows you have)
    If Cells(i, 7).Value = 1 Then  'Loops through column G looking for number 1 (Change the numerical value to fit)
    Cells(i, 1).ClearContents
    Cells(i, 2).ClearContents
    Cells(i, 3).ClearContents
    Cells(i, 4).ClearContents
    Cells(i, 5).ClearContents
    Cells(i, 6).ClearContents
    End If
Next i
End Sub

Jamie

 

[vcoolio]

Hello Corky,

Further to what Jamie has suggested, here are some more methods you could try:-

A) If certain numeric values are known to you and they are the only criteria you are interested in, then you could place them into an array and then filter on them:

Sub Test1()

        Dim ar As Variant, i As Long
        ar = Array(22, 55, 88, 11) '---->Examples only.

Application.ScreenUpdating = False
        
        For i = 0 To UBound(ar)
              With Sheet1.[A1].CurrentRegion
                      .AutoFilter 7, ar(i)
                      .Columns("A:G").Offset(1).ClearContents
                      .AutoFilter
              End With
        Next i
        
Application.ScreenUpdating = True

End Sub

B) If all rows with numeric values in Column G need to be cleared:-

Sub Test2()

Dim lr As Long: lr = Sheet1.Range("A1").CurrentRegion.Rows.Count

On Error Resume Next
Intersect([A:G], Sheet1.Range("G2:G" & lr).SpecialCells(2, 1).EntireRow).ClearContents
On Error GoTo 0

End Sub

C) If you only need to filter on one particular numeric value:-

Sub Test3()

Application.ScreenUpdating = False

        With Sheet1.[A1].CurrentRegion
                .AutoFilter 7, 55  '---->Change number criteria to suit.
                .Columns("A:G").Offset(1).ClearContents
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

With any of the above, any row with a blank in Column G is left alone.

I hope that this helps.

Cheerio,
vcoolio.

 

[Corky4661]

This is all perfect! I was able to extract what I needed to make it work!

Thank you so much for your help.

 

[vcoolio]

You're welcome Corky. Glad we could assist.

Cheerio,
vcoolio.