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.

 

[JLGWhiz]

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.

 

[ShaneB614]

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.

 

[JLGWhiz]

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.

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]

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]

If you are only using the year in cells A1:A2 of the Input sheet then this modified version should work.

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]

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?

 

[JLGWhiz]

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]

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]

JLGWhiz,

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