VBA to identify 3 consecutive empty cells

[Perdi]

Hi all,
I need VBA to identify if three consecutive cells in a column (g) are empty, to enter a value of true into the cell in column b, to then move onto the next row and check.
Then once all rows are checked, I want to delete rows that have true in them

I have tried using the isemtpy , but can't get it to work with multiple cells.

 

[Joe4]

Welcome to the Board!

Which row of the three are you updating?
What if there are more then 3 consecutive blank cells?

In cases like this, it is often very helpful to show us a small sample of your data covering various situations, and show us what the expected output looks like.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Perdi]

Welcome to the Board!

Which row of the three are you updating?
What if there are more then 3 consecutive blank cells?

In cases like this, it is often very helpful to show us a small sample of your data covering various situations, and show us what the expected output looks like.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Joe,

It is the bottom of the three rows, and there will never be more than three consecutive blanks until the end of the information on the sheet.

 

[Joe4]

OK, but why the intermediate step of putting in a True, and then deleting at the end?
Why can't we just delete the rows as we come across them?

 

[Perdi]

OK, but why the intermediate step of putting in a True, and then deleting at the end?
Why can't we just delete the rows as we come across them?

No reason other than that is how I have done it before for other things , I'm very basic at using VBA sorry

 

[Joe4]

So column G may sometimes be empty.
Do you have a column that for every row with data, will always be populated (so we can determine which row to look at to determine exactly where the last data row is)?

 

[Perdi]

No, none of the columns will have data in every row, but in my other macros I normally set it to a limit of 1200 rows, as it will never be longer than that. I am struggling to get a snapshot to upload as it is for work and I am not allowed to go on unauthorised website, or email my sheet outside the company.

If it is easier I did start this project by trying to delete every row below one that contained the word scheduled on column c, but again it wouldn't go down the sheet after it had checked the first row.

 

[Joe4]

As long as the following really is true:

there will never be more than three consecutive blanks until the end of the information on the sheet.

The following code should work:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row

'   Delete row three rows past the last row with data in column G
'   (said there will never be more than 3 blank rows in a row)
    Rows(lr + 3).Delete

'   Loop through all rows starting at bottom, up to row 3
    For r = lr To 3 Step -1
'       Check to see if current row is blank in column G as well as two rows above it
        If (Cells(r, "G") = "") And (Cells(r - 1, "G") = "") And (Cells(r - 2, "G") = "") Then
'           Delete current row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Perdi]

Amazing that worked perfectly, thank you so much for the code and explanations.

 

[Joe4]

You are welcome. Glad I was able to help!