Will blank cells in the table mess up my macro?

andybason

Board Regular
Joined
Jan 7, 2012
Messages
217
Office Version
  1. 2016
Hi guys,

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

VBA Code:
 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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
If you have blanks cells in col A or row 1, then you will not get the entire range
 
Upvote 0
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.
 
Upvote 0
Another option would be
VBA Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
As promised:

Code:
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
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top