Search code examples
excelvbafilterdatatablecalculated-columns

How to calculate off a filtered table in Excel, possibly use VBA

Brand new VBA user here:

I'm trying to have a calculated table form from a filtered table in excel. I know the filtered table only hides values so doing any offset or indexing still returns the hidden filtered values, so the internet has pushed me towards using VBA.

In using VBA I can't seem to plug a variable into a range.value that grows over each loop so the cells grow until the visible filtered table is finished. I believe the Do Until function is what I'm supposed to use to achieve this, but I need some major help on my code, or I'm unaware of an easier process:

Current Broken Code:

Sub TableCreate()

iRow = 1

iPlug = 1

Do Until IsEmpty(Cells(iRow, 1))

Set Rng = Range("A"iPlug)

' Store the current cell value in the dCellValues array

Range(Rng).Value = Cells(iRow, 1).Value

iRow = iRow + 1

iPlug = iPlug + 1

Loop


End Sub

Thank You,

EEE

Solution

  • If you want to use a numeric variable in a cell address, you have to concatenate the number with an ampersand & sign.

    Set Rng = Range("A" & iPlug)

    It also looks like you only need one variable, because iPlug and iRow will always be the same value.

    If you declare the Rng variable as a range, then you can use the variable directly. 

        Dim Rng as Range
'... code leading up to this ...
    Set Rng = Range("A"&iPlug)

    ' Store the current cell value in the dCellValues array

    Rng = Cells(iRow, 1).Value

    You could also use the Range method directly, without using a variable.

    Range("A"&iPlug) = Cells(iRow, 1).Value