Excel VBA Macro - Data out of range column reduction

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
9 columns would be B2:J2.
B2:B10 would Be 9 rows.
Which is is?

Also with the 2020-2028 it is assumed that each header is for only one year. If not please clarify.

If you can arttach a screen shot of your sheet it would help greatly.

Also, if any of the columns deleted are dependent or precedent to formulas in the remaining columns, it could creqate errors upon deletion of the colujmns.
 
Last edited:

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
JLGWhiz,

You are correct, it is B2:J2. Each header corresponds to one year.

The formulas within the columns are dynamically referenced and will not be affected by columns being deleted.

I have attached my output sheet for your reference.

1592436679359.png
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This assumes that the dates in Cells A1:A2 of the Input sheet are full dates and not of a format that would not have a date value. Else the code fails.
VBA Code:
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet, yr As Variant, sd As Variant, ed As Variant
Set sh1 = Sheets("Input") 'Edit sheet name
Set sh2 = Sheets("Calculation") 'Edit sheet name
sd = Year(sh1.Range("A1").Value)
ed = Year(sh1.Range("A2").Value)
    With sh2
        For i = .Cells(2, Columns.Count).End(xlToLeft).Column To 2 Step -1
            yr = .Cells(2, i).Value
            If yr < sd Or yr > ed Then Columns(i).Delete
        Next
    End With
End Sub

Edit the sheet names where I set the sh1 and sh2 variables to make sure the names are correct and for the right sheets. This deletes columns from the Calculation sheet, so the formulas in those columns will be lost .. You should then check the formulas in the columns that shift to be sure they are all still correct before applying this code as for perpetural use.
 

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What do you mean by "full dates"? Do you mean 1/1/2020 or Wednesday, January 1, 2020? Is there any possible way around this so that I only have the year in cells A1 and A2?

I have attempted to run the code but it does not seem to be executing correctly. I run the macro and no columns delete or my entire calculation sheet is deleted. I made sure to adjust the "input" and "calculation" variables to their appropriate names before I attempted to run.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If you are only using the year in cells A1:A2 of the Input sheet then this modified version should work.
VBA Code:
Sub t2
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet, yr As Variant, sd As Variant, ed As Variant
Set sh1 = Sheets("Input") 'Edit sheet name
Set sh2 = Sheets("Calculation") 'Edit sheet name
sd = sh1.Range("A1").Value
ed = sh1.Range("A2").Value
    With sh2
        For i = .Cells(2, Columns.Count).End(xlToLeft).Column To 2 Step -1
            yr = .Cells(2, i).Value
            If yr < sd Or yr > ed Then Columns(i).Delete
        Next
    End With
End Sub
 

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Perfect, thank you. That code above appears to run perfectly now.

If I were to merge my headings into two columns instead of one (see attachment below), what modification would I need to make to my code to ensure that the correct columns are deleted?



1592493611535.png
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I don't do merged cells with vba. There are too many glitches that can occur because the values are not always where they appear to be and vba is not designed to sort it out.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Cross posted Deletion of columns based on criteria - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
JLGWhiz,

Is there a way for me to incorporate an "offset" function and run my macro in that manner?
 

Forum statistics

Threads
1,141,311
Messages
5,705,680
Members
421,405
Latest member
buster015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top