Excel VBA Macro - Data out of range column reduction

[ShaneB614]

I have a calculation sheet with live formulas that contains data with column headings titled 2020-2028 (9 columns) (B2:B10)

On an input sheet, I have START and END dates in two different cells (A1,A2)

With that said, I would like a VBA macro that is able to look at the START and END dates on the input sheet, and based on their presence in the range defined by (A1,A2), delete the columns that are out of range on the calculation sheet.

Theoretically, only year columns within the START and END Year range will be left on my calculation sheet.

Any feedback or further direction is greatly appreciated.

 

[Fluff]

Best solution is, don't use merged cells, they are nothing but trouble. Especially where VBA is concerned.

 

[JLGWhiz]

I think the easiest solution would be to just enter the dates in row 2 twice as shown below. Then the same code woould work.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		2020	2020	2021	2021	2022	2022	2023	2023	2024	2024	2025	2025	2026	2026	2027	2027	2028	2028
3		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
4		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
5		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
6		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
7		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
8		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
9		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
10		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
11		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
12		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
13		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
14		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
15		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
16		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
17		54%	34%	14%	-6%	-26%	-46%	-66%	-86%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
Sheet1

 

[JLGWhiz]

You might want to consider this. Unmerge your cells in your database where you do your calculations and maneuver your columns and rows. Then once you have created what you want for output, do your cell merges or whatever other formatting you desire for your reports. But if you plan to use vba frequently for data manipulation and calculations, I would strongly recomend the minimum of cosmetic formatting because it is a detriment to efficient vba coding. Save the formatting for your output reports, which can also be done with vba after all the data manipulations and calculations are done.