Excel: For End of Month of sales report, need to delete columns for each of the days.

JamesKeenan · December 2012

There's a "Month to Date" sheet where a macro creates a new column and pastes all the values from that day into that column. End of the month, those daily columns are erased.

What we have been doing so far is, end of the month just manually deleting those columns. That's the entire "end of month" process.

I'm trying to google ways to do that automatically. It's harder than I thought it might be. I will probably figure it out in time. I just don't know VBA well enough.

Either I have to retrieve the number of days from the previous month (since the end of day macro would already have been run) or I have to reference the 'date' cell in each daily column, check if it's blank, and delete the rows one at a time like that.

I have no idea.

JamesKeenan · December 2012

Somthing like:

Sheets("MTDFront").Select
ActiveSheet.Unprotect
Range("D3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range.EntireColumn.Delete

Except I can't use Range to delete an entire selection of columns with just a cell highlighted.

I can use End to select highlight all the right columns, but just a cell in each, not the entire column. I don't know how to translate highlighted cells into highlighted columns. If I find that one, I'll be able to delete all the highlighted columns and be done!

The Big Levinsky · December 2012

If your code gets your selection stretched across all the columns you want to kill, then you should be able to execute this code:

Sub KillCols()

    Dim WS As Worksheet
    Dim I As Integer
    
    Set WS = ThisWorkbook.Sheets("MTDFront")
    
    For I = 1 To Selection.Columns.Count
        WS.Columns(Selection.Columns(1).Column).Delete
    Next I
    
End Sub

The Big Levinsky · December 2012

Also, assuming that there can never be more columns than days in a month, and that there's nothing after the columns you want to kill, this should do the whole thing:

Sub KillCols()

    Dim WS As Worksheet
    Dim I As Integer
    Dim rTarget As Range
    
    Set WS = ThisWorkbook.Sheets("MTDFront")
    
    Set rTarget = WS.Range("D3").Resize(1, 40)
    
    For I = 1 To rTarget.Columns.Count
        WS.Columns(rTarget.Columns(1).Column).Delete
    Next I
    
    Set rTarget = Nothing
    Set WS = Nothing
    
End Sub

Basically it takes the starting cell you put in your example. Extends the range out by 40 columns, then goes though and deletes every column in that range one by one.

I can't help but shake the feeling that there's a way to get all the columns you want as a range and then just delete them all without the loop. Been a while since I did VBA in Excel though.

JamesKeenan · December 2012

I think I will try your first idea. The main problem is that I'm trying to keep the "Totals" column that follows the daily columns, so if the first of the month is D, the totals column will be H.

Otherwise, yeah, I'd definitely just

Range("D:AH").Deletethatshit.

The Big Levinsky · December 2012

You know, I don't even think you need the loop. Once you have the selection stretched across the right columns:

Selection.EntireColumn.Delete

Like I mentioned, I'm sorta rusty with Excel.

JamesKeenan · December 2012

I don't have the file at home, when I get back to work I'll use that. Should work.

Thanks a lot.

Penny Arcade

Quick Links

Excel: For End of Month of sales report, need to delete columns for each of the days.

Posts