vba Selecting non-adjacent cells in active row for filldown

[Ike Hunter]

Is there a shorter way of writing VBA to select non-adjacent cells in an active row for the following...

Selecting first empty cell in column A and then selecting the adjacent cells that are in columns B, E, and G and then Filldown the existing formulas.

The following example works however it just seems there could be a better way to write the VBA especially if there are 15 to 20 non adjacent cells on the active row.

[A65000].Select
Selection.End(xlUp)(2, 2).Select
Selection.FillDown

[A65000].Select
Selection.End(xlUp)(2, 5).Select
Selection.FillDown

[A65000].Select
Selection.End(xlUp)(2, 7).Select
Selection.FillDown

Thanks in advance.

 

[Joe4]

Can you show us a sample of what your data currently looks like and expected results?
I think that would go a long way in aiding your explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Ike Hunter]

Hi joe4,
Showing a sample using that tool...
the blue columns have a formula that I would like to have the formulas dragged down with a click of a macro button to row 36 and then I would manually input the data in the white columsn. In my original post I was showing only three of the vba script and not the entire spreadsheet vba script. I was hoping that there was a shorter method for the same result.

If someone can get me started with it, I believe that I could manipulate the script for more/less cells in other workbooks.

Formulas.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
28	3/17/2021	$59.35	-$59.35	$0.00		$71.04	$71.04	$68.50	22.6	30.6	-8	-26%	726	918	-20.92%	$124.00
29	4/17/2021	$71.04	-$71.04	$0.00		$62.26	$62.26	$121.46	20.9	30.1	-9.2	-31%	629	935	-32.73%	$124.00
30	5/19/2021	$62.26	-$62.26	$0.00	$0.59	$70.31	$70.90	$183.79	23.9	31.3	-7.4	-24%	718	909	-21.01%	$124.00
31	6/16/2021	$70.90	-$70.30	$0.60	$0.54	$97.10	$98.24	$0.00	30.5	35.5	-5	-14%	948	1065	-10.99%	$0.00
32	7/19/2021	$98.24	-$98.24	$0.00		$160.17	$160.17	$0.00	46.2	39.7	6.5	16%	1434	1273	12.65%	$0.00
33	8/18/2021	$160.17	-$160.17	$0.00		$131.02	$131.02	$0.00	40.9	47.3	-6.4	-14%	1187	1421	-16.47%	$0.00
34	9/18/2021	$131.02	-$131.02	$0.00		$97.77	$97.77	$0.00	28.2	44.1	-15.9	-36%	933	1323	-29.48%	$0.00
35	10/18/2021	$97.77	-$97.77	$0.00		$62.33	$62.33	$0.00	21.1	27.5	-6.4	-23%	612	880	-30.45%	$0.00
Data

Cell Formulas
Range	Formula
K28:K35	K28	=IF(I28=0,"",(I28-J28))
L28:L35	L28	=I28/J28-1
J33:J35,N28:N35	N28	=M16
O28:O35	O28	=M28/N28-1
B28:B35	B28	=D27+F27+E27
D28:D35	D28	=IF(C28=0,"",(B28+C28))
G28:G35	G28	=IF(F28=0,"",(D28+F28+E28))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L:L	Cell Value	equal above average	text	NO

 

[Joe4]

So, we look at column A to determine how far down we need to drag it, right?
In column B (one of the columns we want to drag down), will the formula just be in one row (row 1 or 2) to start? Or might it appear in any number of rows?

And will the formulas in columns B, D, G, etc always end in the same place to start, or might they all end at different points initially?
(For example, if the last formula in column B is in row 2 will at the start, will that also be true for columns D, G, etc)?
Just trying to figure out if we need to calculate the beginning ending row for each column independently, or whether that is not necessary.

 

[Ike Hunter]

Hi, Joe4,

From original post... please observe the sequence...

This finds the bottom cell of column A. or whatever excel allows today. And you can make it A100, A1001, or A1002, A38 or any cell you want.
[A65000].Select

Then...This finds the first empty cell in column A Ctl/arrow up
Selection.End(xlUp)

Then this selects the adjacent cell which is in column B
(2, 2).Select

Then...
This is the equivalent of CtrlD
Selection.FillDown

Pretend that you put your cursor on cell B36, there was data in it... and you did a CtrD. What would happen?... The command/action/keyboard shortcut... would fill the formula on the next cell down. Selection.FillDown

Do that for every cell that is in a blue column.

I'm just looking for a cleaner vba script other than repeating the same script for every column that requires one (original post) over and over again. Which is not a problem, really! I just thought there must be something better than that.

I hope this helps.

Thanks again

 

[Joe4]

I am not sure that helps, as you tried to explain your original code, but didn't really answer the questions that I asked.
Since we are talking apples and oranges, let's try to come at this a different way.

That image you posted, I am assuming that is the AFTER picture, how you want it to look after it is finished, right?
I am assuming this because all your columns are ending on the same row.
If that is correct, can you post the BEFORE picture?
That is really what I am trying to get information on.

Also, will row 28 ALWAYS be the first row of formulas (and I assumed that they are the same all the way down)?
If we simply know what the first row of formulas is, and how to find the last row we want to copy down to, I think this should be fairly simple.

 

[Marc L]

I just thought there must be something better than that.

As obviously the shorter and better way is to not select anything (99.9999% of the time a good enough VBA procedure does very not need to select)​

but according to the beginner level Excel / VBA basics to just directly use the appropriate range with a single VBA codeline …​

Spoiler

Cells(Rows.Count, 1).End(xlUp).Range("B1:B2,D1:D2,G1:G2,K1:L2,N1:O2").FillDown

 

[Ike Hunter]

Hi, and thank you for helping.
this is what the final result is, in row 36, after clicking the macro assigned image/button. The data in the white columns are manually inputted.
Please note that there are "If" statements in columns D, G, and K.
The data is in a table but I don't think that matters.

Formulas.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
28	3/17/2021	$59.35	-$59.35	$0.00		$71.04	$71.04	$68.50	22.6	30.6	-8	-26%	726	918	-20.92%	$124.00
29	4/17/2021	$71.04	-$71.04	$0.00		$62.26	$62.26	$121.46	20.9	30.1	-9.2	-31%	629	935	-32.73%	$124.00
30	5/19/2021	$62.26	-$62.26	$0.00	$0.59	$70.31	$70.90	$183.79	23.9	31.3	-7.4	-24%	718	909	-21.01%	$124.00
31	6/16/2021	$70.90	-$70.30	$0.60	$0.54	$97.10	$98.24	$0.00	30.5	35.5	-5	-14%	948	1065	-10.99%	$0.00
32	7/19/2021	$98.24	-$98.24	$0.00		$160.17	$160.17	$0.00	46.2	39.7	6.5	16%	1434	1273	12.65%	$0.00
33	8/18/2021	$160.17	-$160.17	$0.00		$131.02	$131.02	$0.00	40.9	47.3	-6.4	-14%	1187	1421	-16.47%	$0.00
34	9/18/2021	$131.02	-$131.02	$0.00		$97.77	$97.77	$0.00	28.2	44.1	-15.9	-36%	933	1323	-29.48%	$0.00
35	10/18/2021	$97.77	-$97.77	$0.00		$62.33	$62.33	$0.00	21.1	27.5	-6.4	-23%	612	880	-30.45%	$0.00
36		$62.33								19.8		-100%		595	-100.00%
37
38
Data

Cell Formulas
Range	Formula
K28:K36	K28	=IF(I28=0,"",(I28-J28))
L28:L36	L28	=I28/J28-1
J33:J36,N28:N36	N28	=M16
O28:O36	O28	=M28/N28-1
B28:B36	B28	=D27+F27+E27
D28:D36	D28	=IF(C28=0,"",(B28+C28))
G28:G36	G28	=IF(F28=0,"",(D28+F28+E28))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L:L	Cell Value	equal above average	text	NO

 

[Ike Hunter]

The current vba for this spreadsheet is...



'Find first empty cell in column B
[B500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column D
[D500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column G
[G500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column J
[J500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column K
[K500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column L
[L500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column N
[N500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column O
[O500].Select
Selection.End(xlUp)(2, 1).Select
Selection.FillDown

'Find first empty cell in column A
[A500].Select
Selection.End(xlUp)(2, 1).Select

 

[Marc L]

The data is in a table but I don't think that matters.

As a table may do not have the same behavior than a classic non table range …​

And far above all for a table no code is needed just inserting a new row all the formulas are automatically created !​