Forum Discussion

Kai El Harrar's avatar
Kai El Harrar
Copper Contributor
Dec 14, 2017
Solved

Use VBA to Autofill a Row until the end of the number of data in another row

Hello,   I need some help with the following problem:   The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that ...
Macros & VBA
Requests
  • Liabilityquek's avatar
    Liabilityquek
    Mar 25, 2019

    Haytham Amairah 

     

    Hi Haytham Amairah,

     

    I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from: 

     

    Selection.AutoFill Destination:=Range("O2:P313")
    Range("O2:P313").Select

     

    to:

    Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

    Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

     

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Aug 16, 2019

To31416

 

It seems that this situation is different!

 

But I think you need to somehow get the total number of rows in the imported reports and then use that number in the macro.

 

If you can attach your file or a sample of it, this is would be helpful!

Thomas2170's avatar
Thomas2170
Copper Contributor
May 11, 2020
Hi ,
I have a situation where I have to autofill towards the right of like consider a cell E4 i have to autofill towards the right of it till it's below row has data.
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 13, 2020

    Thomas2170

     

    Hi,

     

    Please try this code:

    Sub AutoFillToRight()
    Dim lastCellFromRight As String
    ActiveCell.Select

    lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address
    
    If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub
    
    Range(ActiveCell.Address, lastCellFromRight).FillRight
    Range(lastCellFromRight).Select
End Sub

     

    But you need to select the starting cell before you run it which in the example below cell (E3):

     

    Hope that helps

    • Thomas2170's avatar
      Thomas2170
      Copper Contributor
      May 18, 2020
      Thanks@Haytham,

      But i m facing one issue that it is autofilling as 1 I want it be filled in series. Like 1,2,3 and so on
      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        May 23, 2020

        Thomas2170

         

        Hi,

         

        Please use this below instead, and tell me what you think.

        Sub AutoFillToRight()
    Dim lastCellFromRight As String
    ActiveCell.Select

    lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address
    
    If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub
    
    Range(ActiveCell.Address, lastCellFromRight).FillUp
    Range(lastCellFromRight).Select
End Sub

         

        Regards

Resources