I do not understand why my VBA code doesn't work for Excel tables with only one row-CodePudding

clever minds.

I've been working on my code for a while now and there is something weird I do not understand.

But first, let me explain to you what is the situation and what I want my VBA code to do.

I have an Excel spreadsheet with several sheets. One sheet = one business unit of my company.

In each of these sheets, there is a table with data related to the business unit.

I've been coding a VBA program whose goal is to take the data from each table and put them in the same table in a sheet named "All".

It "works" but there is one scenario that can break my code. It is when one of my tables has only one row of data.

Everything works when my tables have no data at all or at least 2 or more. But not if one of them has only one row. And I don't understand why.

Here is my full code:

Sub Final_Macro()
'
' Total_Test1 Macro
'Voir clearcontent pour effacer

    Dim LastRow As Long
    
    Dim LastRowCount As Long
    
    Sheets("All").Select
    
    Range("A3:DZ50000").ClearContents
    
    Application.Goto Reference:="Ref_EPG" 'Goes to the Ref_EPG table and selects it
    
If Not IsEmpty(ActiveCell.Value) Then

    Selection.Copy 'Copies the Ref_EPG table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A3").Select 'Selects cell A3
    
    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    Range("B3").Select 'Selects cell B3
    
    Application.CutCopyMode = False 'Clears the clipboard
    
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,EPG_Table,EPG!R1C,FALSE))=0,"""",VLOOKUP(RC1,EPG_Table,EPG!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
    
    LastRowCount = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A
    
    LastRow = Sheets("All").Range("A" & Rows.Count).End(xlUp).Row
    
    If LastRowCount > 1 Then
    
        Sheets("All").Range("B3").AutoFill Destination:=Sheets("All").Range("B3:B" & LastRow) 'Autofills the VLOOKUP function from B3 to the last used row of Ref_Numbers
    
    End If

End If

'Now let's do the same with Insulation

    Dim LastRow2 As Long
    
    Application.Goto Reference:="Ref_IG" 'Goes to the Ref_IG table and selects it
    
    If Not IsEmpty(ActiveCell.Value) Then
    
    Selection.Copy 'Copies the Ref_IG table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'For Ref_Numbers This function is used to automatically go to the last cell of the table that doesn't have content in it. Should help avoiding overwriting rows

    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    'ActiveCell.Offset(0, 1).Select this line is a possible alternative to the next one. The result is the same but just works differently
    
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select 'Selects the last cell of column B that is not used yet
     
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,IG_Table,IG!R1C,FALSE))=0,"""",VLOOKUP(RC1,IG_Table,IG!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
    
    Application.CutCopyMode = False 'Clears the clipboard

    
    LastRow2 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A
    
    Range("B" & Rows.Count).End(xlUp).Offset(0).Select
    
    If LastRow2 - LastRow > 1 Then
    
        ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow2) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers
    End If
    
End If

'Now let's do the same with Gypsum

Dim LastRow3 As Long

Application.Goto Reference:="Ref_GYPCLG" 'Goes to the Ref_GYP table and selects it

If Not IsEmpty(ActiveCell.Value) Then

    Selection.Copy 'Copies the Ref_GYPCLG table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'For Ref_Numbers This function is used to automatically go to the last cell of the table that doesn't have content in it. Should help avoiding overwriting rows

    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    Range("B" & Rows.Count).End(xlUp).Offset(1).Select 'Selects the last cell of column B that is not used yet

    Application.CutCopyMode = False 'Clears the clipboard

    LastRow3 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A
    
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
     
     ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,GYPCLG_Table,GYPCLG!R1C,FALSE))=0,"""",VLOOKUP(RC1,GYPCLG_Table,GYPCLG!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
    
    If LastRow3 - LastRow2 > 1 Then
    
        ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow3) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers
    
    End If
    
End If

'Now let's do the same with Glass Solutions

Dim LastRow4 As Long

Application.Goto Reference:="Ref_GLASS" 'Goes to the Ref_GLASS table and selects it

If Not IsEmpty(ActiveCell.Value) Then

    Selection.Copy 'Copies the Ref_GLASS table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'For Ref_Numbers This function is used to automatically go to the last cell of the table that doesn't have content in it. Should help avoiding overwriting rows

    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    Range("B" & Rows.Count).End(xlUp).Offset(1).Select 'Selects the last cell of column B that is not used yet

    Application.CutCopyMode = False 'Clears the clipboard

   
    LastRow4 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A
    'Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,GLASS_Table,GLASS!R1C,FALSE))=0,"""",VLOOKUP(RC1,GLASS_Table,GLASS!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
   
   If LastRow4 - LastRow3 > 1 Then
   
        ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow4) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers
    
    End If
    
End If
    
'Now let's do the same with Holdings

Dim LastRow5 As Long

Application.Goto Reference:="Ref_HOLD" 'Goes to the Ref_GLASS table and selects it

If Not IsEmpty(ActiveCell.Value) Then

    Selection.Copy 'Copies the Ref_INSU table

    Sheets("All").Select 'Goes to the "All" tab

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'For Ref_Numbers This function is used to automatically go to the last cell of the table that doesn't have content in it. Should help avoiding overwriting rows

    ActiveSheet.Paste 'Paste the Reference Numbs in the rows

    Range("B" & Rows.Count).End(xlUp).Offset(1).Select 'Selects the last cell of column B that is not used yet

    Application.CutCopyMode = False 'Clears the clipboard

    
    LastRow5 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A
    
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))=0,"""",VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.
    
  
    If LastRow5 - LastRow4 > 1 Then
    
       ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow5) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers
    
    End If
    
End If

    Sheets("All").Select

    Dim LastRow6 As Long
    
    Dim LastColumn As Long
    
    LastColumn = Sheets("All").Range("B2:DC2" & Columns.Count).End(xlToLeft).Row
    
    LastRow6 = Range("B" & Rows.Count).End(xlUp).Row
    
    Range("B3:B" & LastRow6).Select
    
    Selection.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":DC" & LastRow6), Type:=xlFillDefault 'Autofills the formulas in the column selected to the right untill column DB which is the last header of the table
    
    Range("B3").Select
       

End Sub

And here's the part of the code that I want to change because it doesn't work when my table has only one row of data :

Dim LastRow5 As Long

Application.Goto Reference:="Ref_HOLD" 'Goes to the Ref_HOLD table and selects it

If Not IsEmpty(ActiveCell.Value) Then

Selection.Copy 'Copies the Ref_INSU table

Sheets("All").Select 'Goes to the "All" tab

Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'For Ref_Numbers This function is used to automatically go to the last cell of the table that doesn't have content in it. Should help avoiding overwriting rows

ActiveSheet.Paste 'Paste the Reference Numbs in the rows

Range("B" & Rows.Count).End(xlUp).Offset(1).Select 'Selects the last cell of column B that is not used yet

Application.CutCopyMode = False 'Clears the clipboard


LastRow5 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count 'Determines the last row used in column A

Range("B" & Rows.Count).End(xlUp).Offset(1).Select

ActiveCell.FormulaR1C1 = _
    "=IF(LEN(VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))=0,"""",VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))" 'Sets up the VLOOKUP function and if there is nothing in the source cell, returns blank instead of 0.


If LastRow5 - LastRow4 > 1 Then

   ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow5) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers

End If

End If

This portion of my code is used multiple times throughout my program for each business unit of my company.

Everything runs fine in this code until I reach this part of the code:

If LastRow5 - LastRow4 > 1 Then
    
       ActiveCell.AutoFill Destination:=Sheets("All").Range(ActiveCell.Address & ":B" & LastRow5) 'Autofills the VLOOKUP function from the last B cell used to the last used row of Ref_Numbers
    
    End If

The idea here is that I want to put a VLOOKUP formula in my cell (in column B), and if my source table has several rows, the VBA program is supposed to drag the VLOOKUP formula down for each lines that I have in my source table.

To know how many cells needs be filled with the formula, I use two variables, LastRow5 and LastRow4.

By making the difference between these two variables, I know how many cells need to be filled when dragging the VLOOKUP formula down.

That is why I say, if LastRow5 - LastRow4 > 1. Because If there is only one line in my source table, there is no need to drag the formula down.

Do you have any idea why this code doesn't work when my source table has only one row of data? There is something wrong in my code but I don't know what.

Thank you for your help, if you need any additional information to better understand the situation, do not hesitate.

CodePudding user response：

Your calculation of the last row is incorrect:

LastRow5 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Rows.Count

should be

LastRow5 = Sheets("All").Range("A" & Rows.Count).End(xlUp).Row

There is no need to AutoFill, or to Select, or to use ActiveCell. Write the formula to the entire range in one step:

With Sheets("All")
    Dim LastRow As Long
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

    Dim FirstRow As Long
    FirstRow = .Range("B" & .Rows.Count.End(xlUp).Offset(1).Row

    .Range("B" & FirstRow & ":B" & LastRow).FormulaR1C1 = _
        "=IF(LEN(VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))=0,"""",VLOOKUP(RC1,HOLD_Table,HOLD!R1C,FALSE))"
End With