PDA

View Full Version : Hide Rows and Columns



chem101
11-11-2010, 02:22 PM
Hello Everyone,

I'm having trouble hiding row and columns on a spreadsheet. I would like to hide the rows and columns around the range a1:x43. Here is the code I'm using with little success:
Option Explicit
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long

If TypeName(Selection) <> "Range" Then Exit Sub

' If last row or last column is hidden, unhide all and quit
If Rows(Rows.Count).EntireRow.Hidden Or Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If

row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count - 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count - 1

Application.ScreenUpdating = False
On Error Resume Next
' Hide rows
Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
' Hide columns
Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True
End Sub

I selected the range but nothing happens. Any ideas?
Thank you!

mikerickson
11-11-2010, 03:02 PM
If A1:X43 is selected, which rows and columns do you want hidden?

Bob Phillips
11-11-2010, 03:12 PM
Is this what you mean?



Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long

If TypeName(Selection) <> "Range" Then Exit Sub

' If last row or last column is hidden, unhide all and quit
If Rows(Rows.Count).EntireRow.Hidden Or Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If

row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count - 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count - 1

Application.ScreenUpdating = False
On Error Resume Next
' Hide rows
Rows(row1).Resize(row2 - row1 + 1).Hidden = True
' Hide columns
Columns(col1).Resize(, col2 - col1 + 1).Hidden = True
End Sub

chem101
11-12-2010, 01:40 AM
I would like to hide everything except the range selected.
Thank you.

chem101
11-12-2010, 01:42 AM
Thank you, but I want to hide everything but the range a1:x43.

Bob Phillips
11-12-2010, 02:17 AM
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long

If TypeName(Selection) <> "Range" Then Exit Sub

' If last row or last column is hidden, unhide all and quit
If Rows(Rows.Count).EntireRow.Hidden Or Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If

row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count - 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count - 1

Application.ScreenUpdating = False
On Error Resume Next
' Hide rows
Rows(1).Resize(row1 - 1).Hidden = True
Rows(row2 + 1).Resize(Rows.Count - row2).Hidden = True
' Hide columns
Columns(1).Resize(, col1 - 1).Hidden = True
Columns(col2 + 1).Resize(, Columns.Count - col2).Hidden = True
End Sub

chem101
11-12-2010, 11:11 AM
Thanks for all your help, but this isn't working. I'll find another way.
Thank you again for all your effort!! I really appreciate you time!!

mdmackillop
11-12-2010, 11:59 AM
Your original code works for me. What happens when you run it?