When Ranges have blank data

Tags
Range

(This is not a required lesson, but Good Fun anyway.)

The procedure we used to find the end of a data range fails when there are blank cells, like this:

Blank cells

A line like this...

  • Set rDataTable = Range(Cells(2, 1), Cells(2, 1).End(xlToRight).End(xlDown))

... would decide the data ends at row 7, the first blank cell in column 1.

Show all

It's a tricky problem to handle perfectly. The way I would do it is add a loop before the main loop that runs through the rows, finding the first row that is all blank.

It wouldn't be a counted loop (For), but a Do-Until loop. Instead of running a certain number of times, Do-Until loops run until a condition is met. For example, this...

  • Dim tAnswer As String
  • Do
  •     tAnswer = InputBox("Are goattos good?")
  •     tAnswer = LCase(Trim(tAnswer))
  • Loop Until tAnswer = "yes"

... would loop forever until the user had acknowledged the goodness of goattos.

Show all

You can use a loop like that to look for the first row that is all blank. You have to check every cell that might have data in it.

  • Do
  •     Next row
  •     Check if all the columns in the row that might have data are blank
  • Loop Until there is a blank row

For example:

  • Dim tCellValue As String
  • Dim sRowToCheck As Single
  • Dim tIsRowBlank As String
  • Dim sLastDataRow As Single
  •  
  • sRowToCheck = 1 ' Or 2... one less than wherever the data starts.
  • Do
  •     ' Which row to check next?
  •     sRowToCheck = sRowToCheck + 1 ' First time through, this will point to the first data row.
  •     ' Init flag.
  •     tIsRowBlank = "yes"
  •     ' Check for col in row with data.
  •     tCellValue = Trim(Cells(sRowToCheck, 1))
  •     If tCellValue <> "" Then
  •         tIsRowBlank = "no"
  •     End If
  •     tCellValue = Trim(Cells(sRowToCheck, 2))
  •     If tCellValue <> "" Then
  •         tIsRowBlank = "no"
  •     End If
  •     tCellValue = Trim(Cells(sRowToCheck, 3))
  •     If tCellValue <> "" Then
  •         tIsRowBlank = "no"
  •     End If
  •     ' Loop until the flag has stayed at yes.
  • Loop Until tIsRowBlank = "yes"
  • sLastDataRow = sRowToCheck - 1
  •  
  • MsgBox "Last data row: " & sLastDataRow

The flag pattern again.

Show all

If I was doing this for realsies, I'd replace the middle bit with a nested For loop:

  • Sub FindLastDataRow()
  •  
  • Dim tCellValue As String
  • Dim sRowToCheck As Single
  • Dim tIsRowBlank As String
  • Dim sLastDataRow As Single
  • Dim sCol As Single
  •  
  •     Const NUM_DATA_COLS = 3
  •     sRowToCheck = 1
  •     Do
  •         ' Which row to check next?
  •         sRowToCheck = sRowToCheck + 1
  •         ' Init flag.
  •         tIsRowBlank = "yes"
  •         ' Check all the cols that might have data.
  •         For sCol = 1 To NUM_DATA_COLS
  •             tCellValue = Trim(Cells(sRowToCheck, sCol))
  •             If tCellValue <> "" Then
  •                 tIsRowBlank = "no"
  •             End If
  •         Next sCol
  •         ' Loop until the flag has stayed at yes.
  •     Loop Until tIsRowBlank = "yes"
  •     sLastDataRow = sRowToCheck - 1
  •  
  •     MsgBox "Last data row: " & sLastDataRow
  •  
  • End Sub

Change NUM_DATA_COLS to check any number of cols, w/o changing the code. W00t!