VBA dynamic range

Question

Fairly new to VBA. I have a macro that I'd like to change to be able to work on however many rows containing data are in the worksheet rather than the hardcoded value (46).

Sub test1calc() ' ' test1calc Macro ' ' '1 - UNSTRESSED POSTED PRODUCT LEVEL BREAKDOWN SUMMED AT NETTING SET Columns("AS:AS").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AS1").Select Selection.Interior.Pattern = xlSolid Selection.Interior.PatternColorIndex = 2 Selection.Interior.Color = 65535 ActiveCell.FormulaR1C1 = "Unstressed Posted Total" Range("AS2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-30]:RC[-1])" Range("AS2").Select Selection.AutoFill Destination:=Range("AS2:AS46") Range("AS2:AS46").Select ActiveSheet.Calculate Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub

when you say "however many rows are in the worksheet" do you mean the number of rows relative to some existing data? Surely you don't want to fill millions of rows? — SierraOscar
– SierraOscar, Commented Mar 14, 2019 at 22:13
Correct. So ideally the macro would work on 46 rows, 1000 rows, 100000 rows, etc.. Just on however many rows have data. Not blank rows — danny schult
– danny schult, Commented Mar 14, 2019 at 22:15

SierraOscar · Accepted Answer · 2019-03-14 22:24:50Z

I'm assuming you want to fill the rows where there is existing data to the left, not the millions of rows that exist in your worksheet.

If so, I believe your code can be simplified to the following:

Sub test1calc() '1 - UNSTRESSED POSTED PRODUCT LEVEL BREAKDOWN SUMMED AT NETTING SET Columns("AS:AS").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove With Range("AS1") .Interior.Pattern = xlSolid .Interior.PatternColorIndex = 2 .Interior.Color = 65535 .Value = "Unstressed Posted Total" End With With Range("AS2:AS" & Range("O" & Rows.Count).End(xlUp).Row) .FormulaR1C1 = "=SUM(RC[-30]:RC[-1])" .Value = .Value End With End Sub

no worries, don't forget to mark as the answer/upvote if it solved your issue :)

Collectives™ on Stack Overflow

VBA dynamic range

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related