Excel VBA is Finding Every Other Cell not Every Cell From Method

Question

Excel VBA is finding every other cell using a method to check for Empty Cells. On the next time running the same macro, it then finds the cell that it skipped over on the last run while again skipping the next instance of an empty cell. If I cycle through the macro a few times, eventually every row without data is getting deleted, as per the purpose of the macro. The rows do shift upward upon deletion of the row one at a time, I will try a Union and delete the Range as stated by @BigBen

When a cell that is empty is found, it checks columns A, B, and D to see if formula is applied, and if a formula exists in that row, the entire row gets deleted.

Dim cel, dataCells As Range
Dim rngBlank, dc As Range
Dim lastRow, cForm, c, blnkRange As String
Dim cycleTimes As Integer

On Error Resume Next

Set dataCells = Range("F2:W2").Cells    'This is header of the table of data
cycleTimes = dataCells.Count            'Number of times to cycle through macro

For Count = 1 To cycleTimes             'I don't want to cycle through macro
    lastRow = Range("N" & Rows.Count).End(xlUp).Row   'To find end of column
    For Each dc In dataCells
        c = Split(Cells(1, dc.Column).Address, "$")(1)    'Column Letter
        blnkRange = c & "3:" & c & lastRow                'Range to look over for empty cells
        Set rngBlank = Range(blnkRange).SpecialCells(xlCellTypeBlanks).Cells
        For Each cel In rngBlank                          '**Skipping Every other Row**
            If Not TypeName(cel) = "Empty" Then
                cForm = "A" & cel.Row & ",B" & cel.Row & ",D" & cel.Row  'Formula check
                If Range(cForm).HasFormula Then
                    cel.EntireRow.Delete
                End If
            End If
        Next
    Next
Next

You should create a `Union` of ranges to be deleted, and then delete in one step at the end. — BigBen, Feb 06 '20 at 17:28
When you delete a row are the rest of the rows getting moved up (or having their indices lowered)? If so you'll want to mark all to be deleted and do it in one go at the end as BigBen said. — Branson Smith, Feb 06 '20 at 17:29
Voting to close as `General software question`. The posed question *"What in the below code can be changed so this behavior doesn't happen"* would make me believe that you are taking another person's code and do not understand what is happening, so asking someone else to understand the code and make updates. Please update your post to be more specific and provide what *you* have tried thusfar. — Cyril, Feb 06 '20 at 17:51
@Cyril I made comments on the side and the intentions are stated above and answered in the first two comments. — William Humphries, Feb 07 '20 at 15:01
@BransonSmith Yes, the rows are getting moved up with each deletion, I will try a union and delete at the end as BigBen suggested — William Humphries, Feb 07 '20 at 15:02
@BigBen I tried using a Union, but it turns out Intersect worked better for the rows I needed. but the problem is, the rows aren't continuous and Excel doesn't allow me to delete a Range that is not continuous. I may have to do a For Each Loop in Reverse to fix this. — William Humphries, Feb 07 '20 at 20:43
You can most certainly delete rows that aren't continuous. See [this answer](https://stackoverflow.com/a/59975507/9245853) for example, which uses `Union`. — BigBen, Feb 07 '20 at 20:44
@BigBen Yes, you're right, I used Intersect with EntireRow.Delete (xlShiftUp) to achieve this, thank you — William Humphries, Feb 07 '20 at 21:45

score 0 · Answer 1 · answered Feb 07 '20 at 21:50

I was able to use Intersect to find the rows that matched the criteria I was searching for and delete the EntireRow even though the Selection was in separate Rows.

Set dataCells = Range("F2:W2").Cells

lastRow = Range("N" & Rows.Count).End(xlUp).Row  'To find last row to generate range to look through
For Each dc In dataCells                         'Have to perform delete row for every column
    c = Split(Cells(1, dc.Column).Address, "$")(1) 
    blnkRange = c & "3:" & c & lastRow
    Set rngBlank = Range(blnkRange).SpecialCells(xlCellTypeBlanks).EntireRow
    strFormula = "A2:A" & lastRow & ",B2:B" & lastRow & ",C2:C" & lastRow
    Set rngFormula = Range(strFormula).SpecialCells(xlCellTypeFormulas)
    Intersect(rngFormula, rngBlank).EntireRow.Delete (xlShiftUp)  '**THIS helped in deleting Rows**
Next

Excel VBA is Finding Every Other Cell not Every Cell From Method

1 Answers1