Paul, thanks...that work well, but how do I set it up to where I enter a
start # and an ending # in the inputbox? e.g. start with 1 and end with
10?
Thanks again. I appreciate all of your help.
On Thu, Apr 30, 2009 at 7:22 AM, Paul Schreiner <schreiner_p...@att.net>wrote:
> 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 <jhwilson1...@gmail.com>
> *To:* excel-macros@googlegroups.com
> *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 <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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
