unhide fails everytime

[Husky]

I have a macro that hides all columns and rows with no data in them.
To re-edit the thing and fill in the data, I have to unhide those rows and
columns.

I've got the unhide help screen staring me in the face.
if I GOTO a1 and choose unhide from the format menu NADA
if I select b2-> right click and unhide NADA
select B1 format->unhide NADA..

excel 2003.
What am I missing ?

 

[Dave Peterson]

First, I don't see an Unhide option under the Format dropdown.

Are you looking at Format|sheet|unhide, format|column|Unhide,
format|Row|unhide???


If you want to unhide a column (say), you have to select that column first. I
like to just enter a cell from that column into the namebox (to the left of the
formula bar).

Say I want column G unhidden. I type G1 in that namebox.
then format|column|unhide.

Same with any particular row, too.

If I want to unhide everything, I select all the cells on the worksheet, then
format|column|Unhide followed by format|row|unhide.

 

[Husky]

First, I don't see an Unhide option under the Format dropdown.

Are you looking at Format|sheet|unhide, format|column|Unhide,
format|Row|unhide???


If you want to unhide a column (say), you have to select that column first. I
like to just enter a cell from that column into the namebox (to the left of the
formula bar).

Say I want column G unhidden. I type G1 in that namebox.
then format|column|unhide.

Same with any particular row, too.

If I want to unhide everything, I select all the cells on the worksheet, then
format|column|Unhide followed by format|row|unhide.

BTDT. That's exactly what the help says which is why I posted it fails every
time here. Those steps do NOT work after I run the hide empty stuff macro.
After I print out the actual data, I need to unhide everything that was hidden.
and nothing on the helps work.

 

[Peo Sjoblom]

Post your code

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Dave Peterson]

How are you selecting all the cells?

Try using the "button" to the left of the column headers and to the top of the
row numbers.

 

[Husky]

Post your code

Sub printout()
'
' printout Macro
' Macro recorded 09/05/2005 by ken
'

'
Selection.AutoFilter Field:=1, Criteria1:=">=1", Operator:=xlAnd
Range("C,F:F,G:G").Select
Range("G1").Activate
Selection.EntireColumn.Hidden = True
End Sub

Don't really know why. But I can finally get things back with the pulldown in
the 1st column where it says ascending, descending, all, top 10, custom.
All the columns have the same things, but it only works in column 1.

 

[Dave Peterson]

Since you hid the rows by using autofilter, you can reset each field by using
the dropdown arrow and choosing all.

Or you could click on Data|Filter|Show All

 

[Husky]

Since you hid the rows by using autofilter, you can reset each field by using
the dropdown arrow and choosing all.

Or you could click on Data|Filter|Show All

Show all is grayed out now. Though my top row of definitions is now missing

Only worked when I selected column 1.
And actually all the labels in row 1 have now disappeared. and been replaced
with the pull downs. I only had 1 pull down to select rows that actually had a
positive number in them.

I have no idea why this happens but I usually wind up rebuilding the entire
sheet from scratch when this happens.

Just want a simple grocery list of item, aisle, on hand, needed, buy, cost,
rebates, spent.

well I just found my top row of definitions, they were sitting on row 18. Just
below instant potatoes. I would say it got there by sorting since that's
happened b4, except now that I've cut and pasted it back at the top where it's
supposed to be, sorting is now having zero effect on it. It's stuck right there
at the top where I want it. That won't last. I'll turn to see what's on the
tube, and it'll migrate somewhere else..

And all the pull downs have now disappeared. Even the ONLY one I had in column
BUY.

Glad I'm not having this much trouble with access.

 

[Husky]

How are you selecting all the cells?

Try using the "button" to the left of the column headers and to the top of the
row numbers.

I've been to the left, the top, the right, the bottom, the very top left cell
that selects everything.
When I run the macro, I print the results and go shopping.

Come back fill in what's been picked up, costs etc. BUT I have to 1st restore
the view of everything after running that macro.
There's the hangup. The entire sheet view won't return all the time.
Sometimes it's there, sometimes not. Usually when I need to do something with
it is when it's not there.
I've tried just about every help that comes up with the word unhide as the
search word.

 

[Dave Peterson]

"Data|filter|Show All" if your autofilter range is already showing all the
rows--or you don't have autofilter applied in that worksheet.

And I would stay way from using Selection in your code. Using selection means
that the results could vary depending on what was selected.

Maybe you could do all the hiding, printing and unhiding in your code.

I assumed that the quantity was in column A--and the only header row was 1.

Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:F,G:G")
'remove any existing autofilter dropdowns
.AutoFilterMode = False
.Range("a1").EntireColumn.AutoFilter _
Field:=1, Criteria1:=">=1"
ColsToHide.EntireColumn.Hidden = True

.printout preview:=True

ColsToHide.EntireColumn.Hidden = False
'remove the filter
.AutoFilterMode = False
End With

End Sub


I also changed the name of the routine so that it wouldn't match the .printout
method that VBA uses.

Also, I added "Preview:=true" to save paper while testing.

 

[Husky]

"Data|filter|Show All" if your autofilter range is already showing all the
rows--or you don't have autofilter applied in that worksheet.

And I would stay way from using Selection in your code. Using selection means
that the results could vary depending on what was selected.

Maybe you could do all the hiding, printing and unhiding in your code.

I assumed that the quantity was in column A--and the only header row was 1.

Well since I'm having trouble with the sheet this minute, only thing I can say
about your macro is it didn't set the machine on fire.
Other than that all I got was a preview of a blank sheet.
But if I figure out what's going wrong with the sheet, [been fiddling with a
renegade ATI update from windows update] that torched my machine all day. I
haven't been able to even look at the grocery list today.

Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:F,G:G")

I'm guessing the above code may be where the bug is because I have added
columns since the 1st macro code was written.
C = on hand, D = need, E = Buy, F = cost, G = completely blank, just a space
holder now, H, I, J, K and L should be added as columns to hide now.

'remove any existing autofilter dropdowns

the auto filter drop down I had was for on hand. If it found nothing, not much
use in running the macro.
Probably not necessary, but I hadn't figured out how to lock the top row while
scrolling the page down when the original macro was made.

.AutoFilterMode = False
.Range("a1").EntireColumn.AutoFilter _

I should change the range a1 above to E1 for BUY?

Field:=1, Criteria1:=">=1"
ColsToHide.EntireColumn.Hidden = True

.printout preview:=True

ColsToHide.EntireColumn.Hidden = False
'remove the filter
.AutoFilterMode = False
End With

End Sub

Yeah the change I made above to E1 worked to some degree.
The display and column headings 'Aisle, and need' are missing.

Here's your code as I rewrote it.
Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:L")
'remove any existing autofilter dropdowns
.AutoFilterMode = False
.Range("E1").EntireColumn.AutoFilter _
Field:=1, Criteria1:=">=1"
ColsToHide.EntireColumn.Hidden = True

.printout preview:=True

ColsToHide.EntireColumn.Hidden = False
'remove the filter
.AutoFilterMode = False
End With

End Sub

Hmmm. Wonder why it doesn't show columns h, i, j, k and l ?

Well the code above works. Tnx...

Wonder where the headers to the columns went ?

Oh cripes. Wilma is looking like all this is going to be a waste of time. Be
lucky if there's a tree standing in this area, this time next week.
Another Hurricane Charlie headed in.
Hope Winn Dixie got smart and bought a backup generator since last year. Took 6
months for them to restock back to anything resembling normal.

 

[Dave Peterson]

Not starting your pc on fire is usually a good thing!

Replies interspersed.

Well since I'm having trouble with the sheet this minute, only thing I can say
about your macro is it didn't set the machine on fire.
Other than that all I got was a preview of a blank sheet.

The code runs against the activesheet--make sure you're on the correct sheet
when it starts.

But if I figure out what's going wrong with the sheet, [been fiddling with a
renegade ATI update from windows update] that torched my machine all day. I
haven't been able to even look at the grocery list today.

Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:F,G:G")

I'm guessing the above code may be where the bug is because I have added
columns since the 1st macro code was written.
C = on hand, D = need, E = Buy, F = cost, G = completely blank, just a space
holder now, H, I, J, K and L should be added as columns to hide now.

'remove any existing autofilter dropdowns

the auto filter drop down I had was for on hand. If it found nothing, not much
use in running the macro.
Probably not necessary, but I hadn't figured out how to lock the top row while
scrolling the page down when the original macro was made.

Your headers are in row 1 and the data starts in row 2??

Show A1
select A2
window|Freeze panes
Now you'll be able to see row 1 no matter how far you scroll down.

I should change the range a1 above to E1 for BUY?

Yeah the change I made above to E1 worked to some degree.
The display and column headings 'Aisle, and need' are missing.

What does this mean? Is "aisle, and need" a header in one of the columns you're
hiding?

Or do you have multiple header rows?

If you have multiple header rows, you may find it easier to combine the text
into one cell.

Just type the first part, hit alt-enter (to force a new line within the cell)
and keep typing.

Having just one row of headers usually makes things easier for me.

Here's your code as I rewrote it.
Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:L")
'remove any existing autofilter dropdowns
.AutoFilterMode = False
.Range("E1").EntireColumn.AutoFilter _
Field:=1, Criteria1:=">=1"
ColsToHide.EntireColumn.Hidden = True

.printout preview:=True

ColsToHide.EntireColumn.Hidden = False
'remove the filter
.AutoFilterMode = False
End With

End Sub

Hmmm. Wonder why it doesn't show columns h, i, j, k and l ?

You don't see those columns in the printpreview? Or after you dismiss the
printpreview? (You did hide columns F:L.)

Well the code above works. Tnx...

Wonder where the headers to the columns went ?

Oh cripes. Wilma is looking like all this is going to be a waste of time. Be
lucky if there's a tree standing in this area, this time next week.
Another Hurricane Charlie headed in.
Hope Winn Dixie got smart and bought a backup generator since last year. Took 6
months for them to restock back to anything resembling normal.

Make plenty of backups of all your important data--(maybe burn a CD???).

Good luck with the code and the storm.

 

[Husky]

Not starting your pc on fire is usually a good thing!

Replies interspersed.

Well since I'm having trouble with the sheet this minute, only thing I can say
about your macro is it didn't set the machine on fire.
Other than that all I got was a preview of a blank sheet.

The code runs against the activesheet--make sure you're on the correct sheet
when it starts.

But if I figure out what's going wrong with the sheet, [been fiddling with a
renegade ATI update from windows update] that torched my machine all day. I
haven't been able to even look at the grocery list today.

Option Explicit
Sub PrintOutSheet()

Dim wks As Worksheet
Set wks = ActiveSheet
Dim ColsToHide As Range

With wks
Set ColsToHide = .Range("C,F:F,G:G")

I'm guessing the above code may be where the bug is because I have added
columns since the 1st macro code was written.
C = on hand, D = need, E = Buy, F = cost, G = completely blank, just a space
holder now, H, I, J, K and L should be added as columns to hide now.

'remove any existing autofilter dropdowns

the auto filter drop down I had was for on hand. If it found nothing, not much
use in running the macro.
Probably not necessary, but I hadn't figured out how to lock the top row while
scrolling the page down when the original macro was made.

Your headers are in row 1 and the data starts in row 2??

Show A1
select A2
window|Freeze panes
Now you'll be able to see row 1 no matter how far you scroll down.

I should change the range a1 above to E1 for BUY?

Yeah the change I made above to E1 worked to some degree.
The display and column headings 'Aisle, and need' are missing.

Aisle = which aisle the items are in.
need = normal monthly usage of each item, 1-20.

I can send the whole sheet with macros to you.
I finally found what was causing my sorting problem of moving row 1 down below
instant taters.

Seems you can have any type icon style menu bar at the top. and some of them
have the blasted EXACT SAME ICON on them that does 2 different things.
I can't find the toolbar that was there. It wasn't the standard. And only had
one sort icon. sort down.. Can't even recall how I got rid of that bar and
replaced it with the standard, but it's now sorting, locking the top row, and
left column AGAIN.
Believe it or not it's been set up like this b4 by me. And no idea what makes
it screw up causing all this trouble.
Now if I can just get it to print the row 1 headers of the columns not hidden,
things will be back to normal.
It used to do that also..
When something goes south, it really goes..

 

[Dave Peterson]

I don't open attachments.

Which columns are Aisle and Need in?

Don't include them in the range of columns to be hidden. If they're currently
hidden, unhide them.

And make sure your print range includes row 1???

Husky wrote:

 

[Husky]

I don't open attachments.

Which columns are Aisle and Need in?

columns
A B C D E F G H J K L
item aisle onHand need buy cost totals Bought spent rebates
savings

Don't include them in the range of columns to be hidden. If they're currently
hidden, unhide them.

Sub list_final()
'
' list_final Macro
' Macro recorded 10/19/2005 by ken
'

'
Range("B2").Select
Range("A1:K51").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:=">=1", Operator:=xlAnd
Range("C:C,D,F:F,G:G,H:H,I:I,J:J,K:K").Select
Range("K1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
ActiveWindow.SelectedSheets.PrintPreview
End Sub

The above does it all including column headers and sorting by aisle.
Just needed to find out how to get the sheet back to it's original state, and
that was all because of the wrong toolbar sorter showing.

 

[Dave Peterson]

Glad you got it working.

columns
A B C D E F G H J K L
item aisle onHand need buy cost totals Bought spent rebates
savings

Sub list_final()
'
' list_final Macro
' Macro recorded 10/19/2005 by ken
'

'
Range("B2").Select
Range("A1:K51").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:=">=1", Operator:=xlAnd
Range("C:C,D,F:F,G:G,H:H,I:I,J:J,K:K").Select
Range("K1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
ActiveWindow.SelectedSheets.PrintPreview
End Sub

The above does it all including column headers and sorting by aisle.
Just needed to find out how to get the sheet back to it's original state, and
that was all because of the wrong toolbar sorter showing.

 

[Husky]

Glad you got it working.

Yeah wouldn't it be nice if I knew what I did ?

The main fix was restoring the correct toolbar. Trouble was I didn't notice it
had been changed that much. And didn't know which toolbar was actually causing
the sorting problem.

Well I guess as I use excel, I'll pick up more on it's differences from access.