How do you know if a worksheet is empty (blank)? I am using the following code to populate a worksheet.
Sub NoCertOnFile() 'No Cert On File
With Sheets(1)
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A7:A" & Sh1LastRow)
End With
For Each Sh1Cell In Sh1Range 'get data from sheet(1)
If Sh1Cell.Interior.TintAndShade = "0.399975585192419" And _
Sh1Cell.Interior.ThemeColor = xlThemeColorAccent3 Then
With Sheets(5) 'get last row of sheet5
Sh5LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh5Range = .Range("A1:A" & Sh5LastRow)
End With
'copy to sheet(5) next available row
Sh1Cell.EntireRow.Copy Destination:=Sheets(5).Rows(Sh5LastRow + 1)
Sh5LastRow = Sh5LastRow + 1
End If
Next Sh1Cell
'format sheet
Sheets(5).Activate
'formatting removed, not relevant
Sheets(1).Activate
End Sub
If sheet(5) is not blank I want to delete all data and repopulate (trying to prevent duplicate data on sheet 5).
Once I know how to detect a blank worksheet I can then prompt the user via a MsgBox.
Edit:
I will be using coulumns "A" thru "Z" only, row numbers will be variable.
Bookmarks