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 <schreiner_p...@att.net>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 <jhwilson1...@gmail.com>
> *To:* excel-macros@googlegroups.com
> *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 excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
