VBA - Autofill To Last Row of Data

aeddipa

Active Member
Joined
Jun 1, 2009
Messages
337
I'm trying to write a macro that allows me to autofill columns D:G starting always starting at D7 and going until there is no more data found in columns A:C in any subsequent rows. So, in this example the last piece of data is row 578, but next time it may be 800. How do I write the autofill so that it continue until no matter data is left?

Thanks!

Code:
Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("D7:G7").Select
    Application.CutCopyMode = False
    [B]Selection.autofill Destination:=Range("D7:G578")
[/B]    Columns("F:G").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Find the last row of data, set it equal to a variable, and use that variable for your row reference.

If you search the forum archives for finding the last row with data, you should be able to find a ton of hits (it usually comes up multiple times a day!).
 
Upvote 0
Try something like this...
Code:
    Range("D6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
[B]    Lastrow& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/B]
    Range("D7:G7").AutoFill Destination:=Range("D7:G[B]" & Lastrow[/B])
    Columns("F:G").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
 
Upvote 0
Thanks guys! I was able to find another solution that worked. Here is the code for anybody with similar issues.

Code:
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
 
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("D7:G7").Select
    Application.CutCopyMode = False
    Selection.autofill Destination:=Range("D7:G" & LR)
    Columns("F:G").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
 
Upvote 0
Aren't you ONLY testing Column B for LR here? What if Column A or C has data beyond Column B?

FWIW..
Here's code that will return the last nonblank row whether in Col A, B or C...

Code:
Sub MyLastRowUsed()
Set Rng = Range("A:C")
  maxrow = 1
  For Each col In Rng.Columns
    lrow = Rng.Cells(Rows.Count, _
    col.Column).End(xlUp).Row
    If lrow > maxrow Then
      maxrow = lrow
    End If
  Next
  MsgBox maxrow
End Sub
 
Upvote 0
I appreciate the concern with only using column B and I did notice that as well. The fortunate thing is that the data is Column A, B and C will always end on the same row. I used B; however, because it has data in every row unlike column A. Column C could have sufficed. Thank you for the code. I will try to incorporate it.
 
Upvote 0
I believe that AlphaFog's code already take it into consideration, and will take the last row with data in columns A-C without the use of a loop.
 
Upvote 0
Just to let you know that this will also find the last used row in columns A or B or C no matter which column has the last row used.
Code:
    Lastrow = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
Upvote 0
The asterisk means any data. It is a wildcard.

Range("A:C").Find Look in range columns A to C
"*" Look for what? Asterisk is a wild card. (Look for anything)
SearchOrder:=xlByRows search by rows as opposed to columns
SearchDirection:=xlPrevious search bottom up as opposed to top down
.Row return the row number of the first item found.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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