Removing a variable number of columns 1

[LGMan]

Hi, each day I send out the latest 14 days of sales data. However I am forced to obtain the raw data from a system where you can only filter on month, so I have to filter between 2 months to be sure to get a rolling 14 days.
When downloaded to Excel, cols A to H are fixed as these contain various levels of the company hierarchy, then from Col I onwards are the daily sales results. So today the 11th May, would mean I filtered my web report on April and May so that would be 40 columns to cover April 1st to May 10th.
I then manually highlight and delete all but the furthest 14 columns to the right. so today I deleted 26 columns, My final range is then A to V.

Is there a way of doing this within some code, any tips gratefully taken on board.

 

[SkipVought]

Hi,

Any column headings in this 8+40 column table?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

So maybe the number of columns to delete is...

Dim iKeep as integer

iKeep = 8 + Day(Date) + Day(DateSerial(Year(Date), Month(Date), 0))

Range(Columns(iKeep + 1), Columns(ActiveSheet.UsedRange.Columns.Count)).Delete

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Hi, apologies I should have made it clearer. The date will be at the top of each column from Col I onwards, for example "I1" = 01/04/17 and yesterdays sales 11/05/17 appears in "AW1"
So in order to just show show the latest 14 available days, I delete Columns I to AI.

 

[SkipVought]

...and the code I posted?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Okay, the original code I posted just deleted columns containing data for dates > today.

This code also includes deleting columns for columns for dates prior to the last 14 days.

Sub KeepLast14Days()
'SkipVought MAY 12, 2017
'given 8 columns of fixed data followed by last month days and this month days of data
'this procedure deletes all data for dates > today and keeps the last 14 days
'  deleting all days' data prior
    Dim iKeep As Integer
    
    iKeep = 8 + Day(Date) + Day(DateSerial(Year(Date), Month(Date), 0))
    
    Range(Columns(iKeep + 1), Columns(ActiveSheet.UsedRange.Columns.Count)).Delete

    Range(Columns(9), Columns(ActiveSheet.UsedRange.Columns.Count - 14)).Delete
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Hi Skip, you are a true legend on this forum.
The code works a treat and will save me the agro of the manual effort each day, and so much easier for one of my co-workers who is my deignated cover. Your code is also great for me to learn from.

Many Thanks
LG