0

I have a macro that check the value of the last filled row in Column 2 of a table and populates all the previous rows (blank or otherwise) of the Column with the same value.

This code works fine:

Sub Test1()
    Dim cel As Range
    Dim JulyData2 As Range
    Dim ws As Worksheet
    Dim LRR2 As Range
    Dim LRC2 As Long
Set ws = ThisWorkbook.Worksheets("Tracker")
Set JulyData2 = ws.ListObjects("Tb_July2021").ListColumns(2).DataBodyRange
Set LRR2 = JulyData2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
LRC2 = JulyData2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Application.ScreenUpdating = False

For Each cel In JulyData2.Cells
If cel.Row < LRC2 Then cel.Value = LRR2.Value
Next cel

    Application.ScreenUpdating = True
End Sub

Now what I want is for this code to loop through all the columns in the table starting from Column 2 i.e., check for the value of the last filled row in each Column of the table from Column 2 to the last Column and populate all the previous rows of the respective Column with the same value.

I tried this code:

Sub Test2()
Dim tblJuly As ListObject
Dim x As Long

Set tblJuly = ActiveSheet.ListObjects("Tb_July2021")

    Application.ScreenUpdating = False

  For x = 2 To tblJuly.ListColumns.Count
Dim LRR As Range 
Dim cel As Range
    Set LRR = tblJuly.ListColumns(x).DataBodyRange.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

  For Each cel In tblJuly.ListColumns(x).DataBodyRange.Cells
If cel.Row < LRR.Row Then cel.Value = LRR.Value
  Next cel

  Next x

    Application.ScreenUpdating = True

End Sub

But this code gives me a Run-time error '91' Object variable or With block variable not set

It shows an error on this line:

If cel.Row < LRR.Row Then

Where am I going wrong? And how do I correct it?

90sc
  • 13
  • 5
  • You should check if `LRR` is not `Nothing`. Please, insert such a code line after tryin to set it: `Debug.print LRR is Nothing`. What does it return in Immediate Window? – FaneDuru Jul 24 '21 at 19:26
  • It returns False True in immediate window. Ah! I think I see the issue. The 3rd Column has no filled cells. Should I insert an "On Error Resume Next" in the code? Also, where should I insert that line in the code if I do need to insert it? – 90sc Jul 24 '21 at 19:36
  • Okay, I figured it out. I think it needs to be right before the line that is causing the error. – 90sc Jul 24 '21 at 20:01

1 Answers1

0

The code works now. It skips the empty columns causing the error and continues with the next column.

Sub Test2()
    Dim tblJuly As ListObject
    Dim x As Long
    Dim LRR As Range 
    Dim cel As Range

    Set tblJuly = ActiveSheet.ListObjects("Tb_July2021")

    Application.ScreenUpdating = False

    For x = 2 To tblJuly.ListColumns.Count

    Set LRR = tblJuly.ListColumns(x).DataBodyRange.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Not LRR Is Nothing Then
       For Each cel In tblJuly.ListColumns(x).DataBodyRange.Cells
          If cel.Row < LRR.Row Then cel.Value = LRR.Value
              Next cel
          End If
    Next x   
    Application.ScreenUpdating = True    
End Sub
90sc
  • 13
  • 5
  • It works, but it is not the best way of handling with the real problem. Doing it in that way, some other potential errors would be hide... Fortunately, `Find` does not raise an error when it cannot return a range. So, you should check what I tried suggesting in my comment. `If Not LRR Is Nothing Then` your existing loop, followed by `End If`. – FaneDuru Jul 25 '21 at 09:06
  • Thank you for the help. I have edited my code now to replace `On Error Resume Next` with `If Not LRR Is Nothing Then` and closed the If function using `End If` after `Next cel`. – 90sc Jul 25 '21 at 11:15
  • On a different note, stackoverflow informs me that my question already has answers on another post and is giving me an option to delete the question. Does that mean I should be deleting this question or should I just leave it as is? – 90sc Jul 25 '21 at 11:47
  • You can let it as it is. It has already be deleted... Can you edit the answer and adapt it as you said? If yes, do it. If not, it doesn't matter, anyhow... – FaneDuru Jul 25 '21 at 11:51
  • Edited the answer accordingly. Thanks again. – 90sc Jul 25 '21 at 11:57
  • You missed some code lines and indentation was not the most appropriate one. I corrected it. Anyhow, this answer can be seen only by you and users having enough notoriety to see deleted questions... – FaneDuru Jul 25 '21 at 12:07