0

I want to delete a row if there is no value in A. If A86 were to have no value and not have "3060" it would delete. Although Something like row 88 has no value in A but it won't delete. Cell D88 has a formula which is ='labor budget breakout'!D97 and it won't delete. I want these cells D:F to be deleted since they have a value of A="".

Sub DeleteRowIfCostCode()

Dim count, i As Long

count = Sheet1.Cells(Rows.count, "A").End(xlUp).Row

'msgbox count

i = 6
Do While i <= count

If (Cells(i, 1)) = "" Then

Rows(i).EntireRow.Delete
i = i - 1
End If

i = i + 1
count = Sheet1.Cells(Rows.count, "A").End(xlUp).Row

Loop

End Sub

Displays Worksheet

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
Aawin Matriano
  • 1
  • 1
  • 1
    see the answers here for a better solution: https://stackoverflow.com/questions/33744149/code-in-vba-loops-and-never-ends-how-to-fix-this – Scott Craner Mar 31 '21 at 21:35
  • A Cell is an Object, and unnecessary brackets can cause issues with Types. Does `If Sheet1.Cells(i, 1).Value = "" Then` work better? (And `Sheet1.Rows(i).Delete`. Always define your Worksheet, otherwise it defaults to `ActiveSheet`. I recommend reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)) – Chronocidal Mar 31 '21 at 21:36
  • @Chronocidal I tried your recommendation and read your suggested article. The worksheet functions the same but It does not delete those rows with only formula's in them. – Aawin Matriano Mar 31 '21 at 21:51

1 Answers1

0

Having read your code, and looked at your screenshot, I think I've spotted your biggest issue.

It's this line:

count = Sheet1.Cells(Rows.count, "A").End(xlUp).Row

You see, based on your screenshot, count will be set to 86 So, your code will loop through from Row 6 to Row 86, and then stop. This is why it doesn't delete the blank records in Rows 87 through 100. You've (accidentally?) told it not to.

Now, I don't know what the top 5 rows of your worksheet are, but I'm going to assume that you have a Table Header row in Row 5. As such, if you wanted to work out how many rows there are in total you would be better off with something like this:

count = Sheet1.Cells(5, 1).CurrentRegion.Rows.Count + 4 'There are 4 rows before the header

This will use the Range.CurrentRegion property to auto-detect that there is still data in Columns D, E and F, even though there isn't any data in Column A. (While this is a superior method to count the Rows, there is a better way to delete the blank rows — so, we won't actually use that line of code. Otherwise, we're getting into XY Problem territory; it's best to fix our methodology)

Now, as Scott Craner pointed out in a comment, it is better to have your code run a reverse-loop when deleting rows. We can replace your entire Do While with a 7-line If and For statement (or 5 lines, if you use an inline If inside the For loop):

If count >= 6 Then 'prevent infinite loop
    For i=count to 6 step -1
        If Sheet1.Cells(i, 1).Value = "" Then
            Sheet1.Rows(i).Delete
        End If
    Next i
End If

As for the best way to make sure you delete any blank rows after the end? Just delete everything down to the bottom of the worksheet! Full code below:

Sub DeleteRowIfCostCode()
    Dim count As Long, i As Long 'Declare the Type for both variables

    'Safety check, in case there aren't any rows with data. Always at least 6
    count = WorksheetFunction.Max(Sheet1.Cells(Sheet1.Rows.count, 1).End(xlUp).Row, 6)

    'msgbox count 'debug code?

    'Delete any rows after the last row with a value in column A
    Sheet1.Range(Sheet1.Cells(count+1,1), Sheet1.Cells(Sheet1.Rows.Count,1)).EntireRow.Delete

    'Find any blank rows in the middle of the data, working backwards from the end
    For i=count to 6 step -1
        If Sheet1.Cells(i, 1).Value = "" Then
            Sheet1.Rows(i).Delete
        End If
    Next i

End Sub
Chronocidal
  • 6,827
  • 1
  • 12
  • 26