ok... I think I understand.
Try:
'==============================================================
Sub Hide_Rows()
Dim RowNum As Integer
Dim cnt, I
cnt = 0
RowNum = InputBox("Enter Row Index Number to Hide", "Index")
If (RowNum = 0) Then 'Entering "0" UNhides all rows
Cells.EntireRow.Hidden = False
else
For I = 3 To 1000
If (Cells(I, 1) = RowNum) Then
Rows(I & ":" & I).EntireRow.Hidden = True
End If
Next I
End If
End Sub
'==============================================================
________________________________
From: Joe Wilson <[email protected]>
To: [email protected]
Sent: Wednesday, April 29, 2009 1:57:04 PM
Subject: $$Excel-Macros$$ Re: Consolidate Macro
Paul,
Thanks for the response. In the spreadsheet, there is a number holder for a
name to be added later. It's because we have so many linked files and we add
rows for future additions to the model.
So, there are probably 20 instances where I have rows that go from 3 to 18 and
instead of going one by one to hide each row I created the below code and
basically I want to shorten it to where it sees a number 3 to 18 in column A
and hide that row.
I'd also like an input box that pops up and I enter a start number and end
number.
Or if I answer "10", it will hide rows 3-13 ?? 10 will hide any row in column
A that has the number 10.
On Wed, Apr 29, 2009 at 1:07 PM, Paul Schreiner <[email protected]> wrote:
I'm not sure if I understand how you're determining "rows that start with the
number 3 and goes to 18 or higher"
What does "start with number 3" mean?
is the cell in column "A" a number?
Is it always the same as the row number?
or are you saying you want to hide rows that have the number 3,
30,31,32...,300,301... 3000, 3001... because they all start with "3"..
and.. "ask for the number of rows to be hidden"
is this whole thing a way to say that you want to ask the user for the last row
number and hide all rows from 3 to that row number.
Or if I answer "10", it will hide rows 3-13 ??
Will it always be consecutive?
here's some differences:
'==============================================================
Sub Hide_Rows() 'Hide rows from 3 to Specified row
Dim LastRow As Integer
LastRow = InputBox("Enter Last Row Number", "Last Row")
If (LastRow >= 3) Then
Cells.EntireRow.Hidden = False
Rows("3:" & LastRow).EntireRow.Hidden = True
ElseIf (LastRow = 0) Then
Cells.EntireRow.Hidden = False
End If
End Sub
'==============================================================
Sub Hide_Rows2() 'hide X number of rows
Dim LastRow As Integer
LastRow = InputBox("Enter Number of Rows", "Number of Rows")
LastRow = LastRow + 2
If (LastRow >= 3) Then
Cells.EntireRow.Hidden = False
Rows("3:" & LastRow).EntireRow.Hidden = True
ElseIf (LastRow = 0) Then
Cells.EntireRow.Hidden = False
End If
End Sub
'==============================================================
Sub Hide_Rows3() 'hide X number of rows that have a value of 3 or more
Dim LastRow As Integer
Dim cnt, I
cnt = 0
LastRow = InputBox("Enter Number of Rows", "Number of Rows")
If (LastRow >= 3) Then
Cells.EntireRow.Hidden = False
For I = 3 To 1000
If (Cells(I, 1) >= 3) Then
Rows(I & ":" & I).EntireRow.Hidden = True
cnt = cnt + 1
If cnt >= LastRow Then Exit For
End If
Next I
ElseIf (LastRow = 0) Then
Cells.EntireRow.Hidden = False
End If
End Sub
'==============================================================
is one of these close?
Paul
________________________________
From: Joe Wilson <[email protected]>
To: [email protected]
Sent: Wednesday, April 29, 2009 11:37:00 AM
Subject: $$Excel-Macros$$ Consolidate Macro
Hi group. Could someone help me with shortening the below Macro. Basically I
want to hide rows that start with the number 3 and goes to 18 or higher, and
you can see below how I did it the long way.
Is there a way I could have an input box popup that would ask me the # of rows
that I want hidden?
Thanks for your help.
Also, could someone explain Loop to me and an example of how it works. Maybe I
could use the loop function here?
Sub Hide_Rows()
Dim i As Integer
For i = 3 To 1000
Sheets("Total Mo Plus").Select
If ActiveSheet.Range("a" & i).Value = "3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "5" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "6" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "7" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "8" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "9" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "10" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "11" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "12" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "13" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "14" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "15" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "16" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "17" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("a" & i).Value = "18" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
