Hey all,
Long time reader, first time registering and posting. Two questions on some code I am working on:
I have code where I am trying to automate some processing some CSV data. In one section, I am adding two lines, one for =average() and one for =max() for the range of the columns. There use:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[2]C:R[15000]C)"
To select the data. Obviously, 15000 is an arbitrary hard coding. I'd prefer to have it select til the last row for this.
The next thing it does is copy the formulas from those two cells (B2 and B3) and copy the formula across ALL columns.
For this I am currently using:
Selection.AutoFill Destination:=Range("B2:ARD3"), Type:=xlFillDefault
Again, ARD is arbitrary, that just happens to be the longest example in my sample data. How can I make this instead select the last column with data in it?
I also tried this, not sure if it's incorrect or not:
Selection.AutoFill Destination:=Range("B2", Range("B3").End(xlToRight)).Select, Type:=xlFillDefault
A lot of the examples I have found googling this are for a single line (so select B2 and copy to end, but not b2 and b3 to end)
Maybe it would be easier programmatically to do one line at a time?
Long time reader, first time registering and posting. Two questions on some code I am working on:
I have code where I am trying to automate some processing some CSV data. In one section, I am adding two lines, one for =average() and one for =max() for the range of the columns. There use:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[2]C:R[15000]C)"
To select the data. Obviously, 15000 is an arbitrary hard coding. I'd prefer to have it select til the last row for this.
The next thing it does is copy the formulas from those two cells (B2 and B3) and copy the formula across ALL columns.
For this I am currently using:
Selection.AutoFill Destination:=Range("B2:ARD3"), Type:=xlFillDefault
Again, ARD is arbitrary, that just happens to be the longest example in my sample data. How can I make this instead select the last column with data in it?
I also tried this, not sure if it's incorrect or not:
Selection.AutoFill Destination:=Range("B2", Range("B3").End(xlToRight)).Select, Type:=xlFillDefault
A lot of the examples I have found googling this are for a single line (so select B2 and copy to end, but not b2 and b3 to end)
Maybe it would be easier programmatically to do one line at a time?
