Will blank cells in the table mess up my macro?

[andybason]

Hi guys,

I have written a macro with the following line to convert a dynamic range into a table:

 ThisWorkbook.Sheets("FilteredBats").ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "FilteredBatsTable"

It seems to work but it is possible that in future the range will contain blank cells. Will this cause a problem with the code or will it still capture and convert the entire range?

Thanks

 

[Fluff]

If you have blanks cells in col A or row 1, then you will not get the entire range

 

[pbornemeier]

This will select from A1 to the last cell, but it will include some blank rows or columns if the worksheet had previously contained more rows or columns that it currently does
Range([A1], [A1].SpecialCells(xlCellTypeLastCell))

If you have a sparse range, the only way to absolutely ensure that you get the last row and column would be to check in the used range each column for its last filled row and each row for its last filled column.

 

[Fluff]

Another option would be

Sub andybason()
   Dim UsdRws As Long, UsdCols As Long
   With Sheets("FilteredBats")
      UsdRws = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).Row
      UsdCols = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
      .ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(UsdRws, UsdCols)), , xlYes).Name = "FilteredBatsTable"
   End With
End Sub

 

[pbornemeier]

This inelegant function is the initial way I figured to return the rectangular region taken up by sparsely populated cells if it was not sure that the field started in A1. It is likely that it could be rewritten more concisely taking the previous post in mind:

Function ReturnFilledRegion(wks As Worksheet) As Range

    Dim rngUsedRange As Range
    Dim lLastRow As Long
    Dim lLastCol As Long
    Dim lFirstRow As Long
    Dim lFirstCol As Long
    Dim lIndex As Long
    Dim lCurrRow As Long
    Dim lCurrCol As Long
    Dim sText As String
    
    With wks
        Set rngUsedRange = .UsedRange
        lLastRow = 0
        For lIndex = rngUsedRange.Column To rngUsedRange.Columns.Count + rngUsedRange.Column - 1
            lCurrRow = .Cells(.Rows.Count, lIndex).End(xlUp).Row
            sText = .Cells(lCurrRow, lIndex).Text
            If lFirstCol = 0 And lCurrRow > 0 And Len(sText) > 0 Then lFirstCol = lIndex
            If lCurrRow > lLastRow Then lLastRow = lCurrRow
        Next
        For lIndex = rngUsedRange.Row To rngUsedRange.Rows.Count + rngUsedRange.Row - 1
            lCurrCol = .Cells(lIndex, .Columns.Count).End(xlToLeft).Column
            sText = .Cells(lIndex, lCurrCol).Text
            If lFirstRow = 0 And lCurrCol > 0 And Len(sText) > 0 Then lFirstRow = lIndex
            If lCurrCol > lLastCol Then lLastCol = lCurrCol
        Next
        
        Set ReturnFilledRegion = .Range(.Cells(lFirstRow, lFirstCol), .Cells(lLastRow, lLastCol))
    End With
    
End Function

 

[pbornemeier]

As promised:

Function ReturnFilledRectangularRegion(wks As Worksheet) As Range

    Dim lLastRow As Long, lLastCol As Long
    Dim lFirstRow As Long, lFirstCol As Long

   With wks
      lLastRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).Row
      lLastCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
      lFirstRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).Row
      lFirstCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlNext, , , False).Column
      Set ReturnFilledRectangularRegion = .Range(.Cells(lFirstRow, lFirstCol), .Cells(lLastRow, lLastCol))
   End With
   
End Function

 

[andybason]

Thanks guys, I really appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback