Clear Contents Macro

[spyldbrat]

I am a VERY beginner on macro's and can read minimal coding of a macro.

I found a macro on the internet that is supposed to clear the contents of all rows that contain specific data in a specific column. Of course, I had to modify the coding to match what my data. My data starts on row 8. I want it look at column H and if the word "null" exists, I want it to erase all the data in that row (either clear contents or deleting rows - I am not particular on this one). When I run the macro, it erase all data, starting from row 8 even if "null" does not exist.

Sub Clear_Contents()
For i = 8 To 1500
If Range("h" & i) <> "null" Then
Range("A" & i & ":M" & i).ClearContents
End If
Next i
End Sub


Also, my spreadsheet has multiple tabs (taken from another macro: "NY Reg", "NY Lic & REg", """Skip"" in Ramco", "Annual", "90 Day", "Support", "NY Term", "PA Stmt #", _
"PA Print Card Corp", "PA Print Card State", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", "Shannon"

Would I be able to add to the top of the macro the following so it would run on each tab at all at once or would I have to list each tab individually in the macro:

Sheets(Array("NY Reg", "NY Lic & REg", """Skip"" in Ramco", "Annual", "90 Day", "Support", "NY Term", "PA Stmt #", _
"PA Print Card Corp", "PA Print Card State", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", "Shannon")).Select
Sheets("NY Reg").Activate

 

[Nine Zero]

Hello,

I believe the error might be in the Range line i believe it might be

If Range("H" & i).Value = "null" Then


​Give that a try

 

[Fluff]

This will look at all worksheets in the workbook.
At present it will simply highlight any row to be deleted in red, so that you can check it's working ok.
If all ok then replace the part in red with .Delete

Sub DelRws()
' spyldbrat

    Dim Ws As Worksheet
    Dim Cnt As Long
    
Application.ScreenUpdating = False

    For Each Ws In Worksheets
        For Cnt = Ws.Range("H" & Rows.Count).End(xlUp).Row To 8 Step -1
            If UCase(Ws.Range("H" & Cnt).Value) = "NULL" Then Ws.Rows(Cnt)[COLOR=#ff0000].Interior.Color = vbRed[/COLOR]
        Next Cnt
    Next Ws

End Sub

 

[spyldbrat]

I got a syntax error....

 

[spyldbrat]

The DelRows one did not work at all. It left all rows even those with "null".

 

[Fluff]

The DelRows one did not work at all. It left all rows even those with "null".

Were any of the rows highlighted in red?
if not, can you put a copy of your file on OneDrive, or similar & place a link to it in this thread

 

[spyldbrat]

Yes they were, but I am looking to just have the rows entirely deleted. There are too many tabs for me to sort by color and delete.

 

[Fluff]

Yes they were, but I am looking to just have the rows entirely deleted. There are too many tabs for me to sort by color and delete.

In that case I suggest you read my comments just above the code in post#3, especially the 2nd & 3rd lines.

 

[Eric W]

Here's a non-looping version that will delete every row that has "null" in column H. It should be pretty fast.

Sub DelNulls()

    Columns("H:H").Replace What:="null", Replacement:="#N/A", LookAt:=xlWhole, MatchCase:=False
    Columns("H:H").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End Sub

This does assume that you won't have any actual errors in column H. It can also be put in a loop like Fluff did to handle multiple sheets. Do you want to have a specific list, or can you apply this to every sheet, or are there some sheets in your workbook that you don't want to apply this to?

 

[spyldbrat]

Eric,

This works perfect!!! Thank you very much. Since I don't want it to apply to every sheet, can I just update the coding to this?


Sheets(Array("NY Reg", "NY Lic & REg", """Skip"" in Ramco", "Annual", "90 Day", "Support", "NY Term", "PA Stmt #", _
"PA Print Card Corp", "PA Print Card State", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", "Shannon")).Select
Sheets("NY Reg").Activate
Columns("H:H").Replace What:="null", Replacement:="#N/A", LookAt:=xlWhole, MatchCase:=False
Columns("H:H").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete