INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Removing a variable number of columns

Removing a variable number of columns

(OP)
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.

RE: Removing a variable number of columns

Hi,

Any column headings in this 8+40 column table?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Removing a variable number of columns

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Removing a variable number of columns

(OP)
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.

RE: Removing a variable number of columns

...and the code I posted?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Removing a variable number of columns

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.

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Removing a variable number of columns

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close