Loop over columns, returning row numbers with empty cells

[Christiaan]

Hello all.

I am trying to compile a VBA-code that will go down a column, find an empty cell, write the row-number and continues.
Once the first column is finished, it should 'jump' to the next column, until there are no more columns left.

I did get the code working to write the empty cell row-numbers, including the offset. But I'm getting stuck on looping over the columns.
Any ideas would be greatly appreciated!

This is the code I have so far:

Sub FindEmptyRowNumbers()

Dim i As Integer
Dim j As Integer
Dim RowCount As Integer

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

Worksheets("References").Range("A8").Select
j = 1
For i = 1 To RowCount
    If Worksheets("Sheet1").Range("C" & i).Value = "" Then
    ActiveCell.Offset(0, j).Value = i
    j = j + 1
    End If
Next

End Sub

 

[Joe4]

Your code looks a little flaky to me. I tested it out, and it does not seem to work very well (it was returning incorrect numbers, and was writing across the column for things it finds down the row).

Can you tell if more detail exactly how you want this to work?
Specifically,
- is "Sheet1" the sheet you want to search?
- is "References" the sheet you want to write the results to?
- how exactly do you want these results written (starting cell & direction)?
- with multiple columns, do you still only want to return the row reference, or the column reference as well?

 

[Christiaan]

Hey Joe, thanks for your response.

What I want it to do:
- Check column A on sheet1 (for example) and write each row number that has a blank cell onto the 'references' sheet.
- When column A is finished, repeat the process for column B.
- When column B is finished, repeat the process for column C.

The code I have so far works fine on my worksheet, but for privacy reasons I renamed the sheets in the code.

I was thinking to use something like a 'For each column', but my mind went blank. LOL

 

[Dim Me as xlNoob]

It still seems a little confusing, however may I offer a solution for how I understood the question:

Sub FindEmptyRowNumbers()
    Dim sh1 As Worksheet, shRef As Worksheet
    Dim i As Long, j As Long, lngRow As Long, lngCol As Long, iRow As Long, iCol As Long
    
    Set sh1 = Worksheets("Sheet1")
    Set shRef = Worksheets("References")
    
    lngRow = sh1.UsedRange.Rows.Count
    lngCol = sh1.UsedRange.Columns.Count
    
    jCol = 0
    For j = 1 To lngCol
        iRow = 0
        For i = 1 To lngRow
            If sh1.Cells(i, j).Value = "" Then shRef.Cells(8 + iRow, 1 + j + jCol).Value = i: iRow = iRow + 1
        Next i
        iCol = iCol + 1
    Next j
End Sub

This will check each cell in Sheet1 and consolidate a list of the empty row numbers in the corresponding column on the References sheet (plus 1 since you started yours from cell B8), starting from row 8. So you are left with a table in the end of empty row numbers by column.

 

[Christiaan]

Hello Dim Me,

This code works perfectly, thank you so much for the suggestion.
I'd like to make one small adjustment... Where/ how can I define where the table is being written? I found the row number, but where to define the column number to start?

Warm regards,

 

[Dim Me as xlNoob]

Glad to help,

In the following line:

If sh1.Cells(i, j).Value = "" Then shRef.Cells(8 + iRow, 1 + j + jCol).Value = i: iRow = iRow + 1

shRef.Cells(8 + iRow, 1 + j + jCol)

The 8 adjusts where the starting row is, and the 1 adjusts where the starting column is.