CODEWITHSUNDEEP
excelvbaautofill
user3716558
user3716558

Reputation: 11

VBA Finding Lastrow using Lastcolumn

I want to be able to determine the last row of a specific column. However, this column is constantly and consistently changing. Therefore, I use a variable lastcolumn2 to determine the column I need to figure out. I then need to auto fill this column once I figure out the last row. My current code is as follows:

Dim lastColumn2 As Long
lastColumn2 = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Dim Lastrow2 As Long
Lastrow2 = Range(lastColumn2 & Rows.Count).End(xlUp).Row

Cells(2, lastColumn2 - 1).Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>3,isnumber(RC[-1])),10,IF(AND(VLOOKUP(RC[-4],R1C[-11]:R50000C[-6],5,FALSE)=10,RC[-1]<3),""Depart"",0))"
Selection.AutoFill Destination:=Range(Cells(2, lastColumn2 - 1), Cells(785, lastColumn2 - 1))
Range(Cells(2,lastcolumn2,).AutoFill Destination:=Range(Cells(2,lastcolumn2):lastcolumn2 & lastRow)

Upvotes: 0

Views: 2862

Answers (3)

user3716558
user3716558

Reputation: 11

I ended up figuring out my own method but I appreciate the help!

Code seen here:

Dim lastColumn2 As Long
lastColumn2 = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Cells(1, lastColumn2 - 1).Select ActiveCell.FormulaR1C1 = "Charge"

Dim Lastrow2 As Long
Lastrow2 = Worksheets(1).Cells(Rows.Count, lastColumn).End(xlUp).Row

Cells(2, lastColumn2 - 1).Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>3,isnumber(RC[-1])),10,IF(AND(VLOOKUP(RC[-4],R1C[-11]:R50000C[-6],5,FALSE)=10,RC[-1]<3),""Depart"",0))"
Selection.AutoFill Destination:=Range(Cells(2, lastColumn2 - 1), Cells(Lastrow2, lastColumn2 - 1))

Upvotes: 0

WGS
WGS

Reputation: 14179

I think you're confusing yourself with the necessary approach. If you want to get the last column to the right, your approach is sound. However, if you're trying to get the last row from that column, using the variable for last column is not a good reference. Why?

This is because your last column is your target to fill in the first place. If you're trying to put in a formula there, either get a secondary reference to anchor your code on to get the necessary last row.

For example, you have the following data set:

enter image description here

Assume we want to put in formulas in D to get the sum of A, B, and C. However, this column of sums can either be in D, E, or F. We're never sure about it's position, only that we need the sum of A, B, and C and that this sum of columns is always the furthest to the right.

To get the column index of the last column, a simple variation of the following should suffice:

Cells(1, Columns.Count).End(xlToLeft).Column

In our example, this will return 4, which is the column index of D. To get the last valid row, however, we shouldn't get it from D. Observing simply will tell us that the last row in D is just 1. This would have been nice if you already have values in D prior to executing the code -- only then will it be a valid reference. However, best practices shun this approach: you should always be preparing for moments where your target column will have no values.

The best place to get the last row's index is any of the columns A, B, or C. Changing the above code, we can get the last row's index via:

Cells(Rows.Count, 1).End(xlUp).Row

I've taken the liberty of assuming that the best reference is Column A, which will explain the Cells(.Rows.Count, 1) part.

Now, armed with those two variables, rather than using AutoFill, I'd much rather create a range and directly input a formula inside each of that range's cells. I can create a range that covers the necessary cells in column D as follows:

Set Rng = Range(Cells(2, LCol), Cells(LRow,LCol))

The above roughly translates -- based on our expectations -- to Set Rng = Range("D2:D10"). From there, everything is child's play with a simple formula trick that automatically adjusts itself.

See the following code, complete with qualifiers and applying the above logic.

Sub Rarara()

    Dim WS As Worksheet: Set WS = ThisWorkbook.Sheets("Sheet1") 'Change as necessary.
    Dim LRow As Long, LCol As Long
    Dim RngTarget As Range

    With WS
        LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set RngTarget = .Range(.Cells(2, LCol), .Cells(LRow, LCol))
        RngTarget.Formula = "=SUM(A2,B2,C2)"
    End With

End Sub

Notice that I've used With here. Also, see the last line before the closing line of this With block. See how I equated multiple cells to a seemingly static formula? Well, in this case, Excel is smart enough to "spread" it properly across the range. See following screenshot after running the code:

enter image description here

Ta-da! Let us know if this helps.

Upvotes: 1

Maury Markowitz
Maury Markowitz

Reputation: 9283

I always use the "reverse find" method because it's the only one that works reliably. Try this...

LastRow = Range(lastCol2 & 65536).Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

You may have to play with the selector in the range a bit.

Upvotes: 0

Related Questions