In one of my monthly reports, I needed to delete all empty cells of a specific column but not before to check in another column if the date is showing the current month. In fact, once it finds them, it is deleting the entire row, I mean, it is not deleting only the empty cell.
When I use the macro ?
To delete the entire row of an empty cell only for the current month.
How to create the macro ?
Read How to create, edit, hide and select a macro in an excel report
How to create the button to associate it with the macro ?
Read How to create a button and associated it to a macro in an excel report
How is the macro ?
Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.
Sub test()
Dim i As Integer
' starting to check from row 2
i = 2
' change the column number 2 (column B) by the column number of your date column and change 11 by the number of rows to check
Do While (Cells(i, 2).Value <> "") And (i < 11)
If VarType(Cells(i, 2)) = vbDate Then
If Cells(i, 2) >= Int(DateSerial(Year(Date), Month(Date), 1)) Then
' change the column number 1 (column A) by the column number to check empty cells then deleting the row of the empty cells
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1).EntireRow.Delete
End If
End If
End If
i = i + 1
Loop
End Sub
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...