Fill Law Row Across Columns

[quantmaven]

Hi!
I have monthly time series from column A to AU. Every month I need to fill the last row. The code below works for ALL columns. How can I adapt this code so it does the same just from column A to AU? Thanks in advance!

Sub Fill_Lastrow_AcrossColumns()
Application.ScreenUpdating = False
Dim Ccol As Long
Dim c As String
Dim Dws As Worksheet
Set Dws = Sheets("DATA")
LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
For Ccol = 2 To LastCol
    c = Replace(Split(Columns(Ccol).Address, "$")(1), ":", "")
    lrow = Sheets("DATA").Range(c & "65536").End(xlUp).Row
    Sheets("DATA").Range(c & lrow).Copy Destination:=Sheets("DATA").Range(c & lrow + 1)
Next
End Sub

 

[PhilS2520]

Hi quantmaven,

You see the line starting with LastCol =... this line figures out the last column occupied with data. If you want to limit the processing to only columns A through AU, then you can comment this line out, and hard code the addresses of columns A = 1 and and U = 20 and Z = 26 and AU = ??? You can count them on your own.

Or you can change the For-Next loop counter to the value of column AU.

Hope this helps.

 

[Fluff]

How about

For Ccol = 2 To 47
    Lrow = Sheets("DATA").Cells(65536, c).End(xlUp).row
    Sheets("DATA").Cells(Lrow, c).Copy Destination:=Sheets("DATA").Cells(Lrow + 1, c)
Next

Or if all columns are the same length you can use

Sub Fill_Lastrow_AcrossColumns()
Application.ScreenUpdating = False
Dim Dws As Worksheet
Dim Lrow As Long
Set Dws = Sheets("DATA")
Lrow = Dws.Range("A" & Rows.Count).End(xlUp).row
Dws.Range("A" & Lrow).Resize(2, 47).FillDown
End Sub

 

[quantmaven]

Phil & Fluff, thanks your suggestion worked! Last question: If I want to apply this code to multiple sheets except Sheet 1 and Sheet 2, how does the code fit inside this one? I'm all done after this. Thanks alot for your time I am greatful.

Sub Fill_Lastrow_AcrossColumns()
Application.ScreenUpdating = False
Dim Dws As Worksheet
Dim Lrow As Long
Set Dws = ActiveSheet
Lrow = Dws.Range("A" & Rows.Count).End(xlUp).Row
Dws.Range("A" & Lrow).Resize(2, 47).FillDown
End Sub

Sub Fill_Lastrow_AcrossColumns()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' Exclude certain sheets
If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then
' Do your thing here
End If
Next ws
End Sub

 

[Fluff]

Simply replace ' Do your thing with

Lrow = Ws.Range("A" & Rows.Count).End(xlUp).row
Ws.Range("A" & Lrow).Resize(2, 47).FillDown