Excel Geeks.

[Ezekiel 25:17]

Right I know there must be a way to do this, but I don't know it, is their a way to shortcut to a specific range of columns, from a set format of data, a quick parts type of thing, instead of having to manually hide them one by one every time ?? thanks.

[Pidge]

You manage to sort this?  Might be able to help, but not 100% on what you're after. Might need a few details about the input data and what processing you're looking to do with it. 

[Ezekiel 25:17]

No mate, it's nothing urgent, basically every day I get a data dump with columns a-z and beyond, but I only need the data from about 7 columns so I'm manually hiding about 16 columns every time, tedious.

 

I don't want to manipulate anything, it's all literally just for filtering and viewing.

[Mudface]

Do you hide the same columns each time? If so, you could record a macro (in the personal macro workbook, so it's always available) while you're hiding the usual ones, and run that each time maybe?

[Ezekiel 25:17]

15 minutes ago, Mudface said:

Do you hide the same columns each time? If so, you could record a macro (in the personal macro workbook, so it's always available) while you're hiding the usual ones, and run that each time maybe?

Yeah, same columns, this is what I need to do, I think...

[Mudface]

OK, you'll probably end up with something like this-

 

Sub Macro1()
    Range("D:D,J:J,K:K,L:L").Select
    Selection.EntireColumn.Hidden = True
End Sub

 

But you don't have to select objects in VBA to manipulate them, so you can shorten it to the below, and change the name to something meaningful. Then just run it from the Macro menu whenever you open the daily workbook.

 

Sub HideColumns()
    Range("D:D,J:J,K:K,L:L").EntireColumn.Hidden = True
End Sub

 

[Ezekiel 25:17]

Thanks Fellas, really helpful, going to have a play around with it now.

[Pidge]

Yeah, if it the hiding bit that was key I was going to suggest a macro. Never properly learned how to write them so was just going to suggest experimenting with recording one (as trial and error is my approach).

 

Another way is to set up a two tab spreadsheet, one which you'll dump the raw data in, and the second with lookups for your relevant columns (just =B2 etc, would do it). Bit rough and ready, but quick. Not robust enough to share, but useful for quick reviews/troubleshooting if you know the data.

[Mudface]

1 hour ago, Pidge said:

Yeah, if it the hiding bit that was key I was going to suggest a macro. Never properly learned how to write them so was just going to suggest experimenting with recording one (as trial and error is my approach).

 

Another way is to set up a two tab spreadsheet, one which you'll dump the raw data in, and the second with lookups for your relevant columns (just =B2 etc, would do it). Bit rough and ready, but quick. Not robust enough to share, but useful for quick reviews/troubleshooting if you know the data.

They're actually quite interesting to write, there's a ridiculous amount you can with them, probably too much to be honest, hence why they tightened up security on them.

[Furmedge]

Is this data dump coming from an internal source or external provider?  Sounds like something SQL is perfect for if from an internal system.  I went on a 2 day SQL course and was able to pull out Oscar winners and movies by year, actor and director on a whim, but then I went back to the office and it was a fuck load more difficult when using it for real work.  Rather than go on another course we employed somebody else to do it.  *dusts hands*

[Ezekiel 25:17]

23 hours ago, Furmedge said:

Is this data dump coming from an internal source or external provider?  Sounds like something SQL is perfect for if from an internal system.  I went on a 2 day SQL course and was able to pull out Oscar winners and movies by year, actor and director on a whim, but then I went back to the office and it was a fuck load more difficult when using it for real work.  Rather than go on another course we employed somebody else to do it.  *dusts hands*

Internal, I defo need to go on one of them courses.

[Ezekiel 25:17]

You'll probably all chuckle at how hilariously short sighted this is, it asked me to add a short cut for the recorded Macro, I chose ctrl f, which means when I went onto to all the other excel sheets I couldn't shortcut to find data because I'd replaced that shortcut, oh dear, I've muddled about and fixed it now.