Excel VBA Last Row / Column Script?-CodePudding

I'm trying to get the correct ranges for a program that's supposed to find totals on a given sheet. I'm struggling a little bit here with the syntax.

I want to always select one column outside of the last column with data, and then autopopulate that column with data starting at Row 4. I'm doing something wrong here.

Dim LastColumn As Long
Dim LastRow As Long

LastColumn = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Cells(LastColumn   1).Select
    ActiveWorkbook.ShowPivotTableFieldList = False
    Selection.Copy
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Percent Total"
    ActiveCell.FormulaR1C1 = "=RC[-1]/R34C[-1]"
    Selection.AutoFill Destination:=Range(LastColumn & "4" & ":" & LastColumn & LastRow)

I'm getting a Syntax error. AutoFill Method of Range class failed so I know it's that line.

Here's what I'm looking for. In the example, Column AD is created and filled with the data which is simple division.

Picture of Data

CodePudding user response：

It is easier to work with Cells here and to avoid using ActiveCell and Select/Selection. Also there is no need to use AutoFill, since you can write a formula to a multi-cell range in one step.

With ActiveSheet
    LastColumn = .Cells.Find("*", _
                     SearchOrder:=xlByColumns, _
                     SearchDirection:=xlPrevious).Column
    LastRow =  .Cells.Find("*", _
                   SearchOrder:=xlByRows, _
                   SearchDirection:=xlPrevious).Row
    .Cells(4, lastColumn   1).Value = "Percent Total"

    Dim rng As Range
    Set rng = .Range(.Cells(5, lastColumn   1), .Cells(lastRow, lastColumn   1))

    rng.FormulaR1C1 = "=RC[-1]/R" & lastRow & "C[-1]"
End With

CodePudding user response：

Maybe something like this ?

Sub test()
Dim FirstData As Range: Dim div As Range: Dim rg As Range
Set FirstData = Cells(4, Columns.Count).End(xlToLeft).Offset(1, 0)
Set div = FirstData.End(xlDown)
Set rg = Range(FirstData, div.Offset(-1, 0)).Offset(0, 1)
FirstData.Offset(-1, 1).Value = "Percent Total"
rg.Value = "=" & FirstData.Address(0, 0) & "/" & div.Address
End Sub

FirstData variable is to get the cell below "Grand Total".
div variable is to get the cell of the Grand Total for columns of the pivot table.
rg variable is to get the range which is going to be filled with formula.

The formula is not using R1C1, but cell address.

If you don't use "Show grand total for columns" in your pivot table option, then the rg variable like this : Set rg = Range(FirstData, div).Offset(0, 1)