Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

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

JamesKeenanJamesKeenan Registered User regular
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.

Posts

  • JamesKeenanJamesKeenan Registered User regular
    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 LevinskyThe Big Levinsky Registered User regular
    edited 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 on
  • The Big LevinskyThe Big Levinsky Registered User regular
    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.

  • JamesKeenanJamesKeenan Registered User regular
    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 LevinskyThe Big Levinsky Registered User regular
    edited 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.

    The Big Levinsky on
  • JamesKeenanJamesKeenan Registered User regular
    I don't have the file at home, when I get back to work I'll use that. Should work.

    Thanks a lot.

Sign In or Register to comment.