how do you WANT to do it?
You can use two input boxes:
Startnum = Inputbox("Enter Lower Index Number","Index-Lower")
Stopnum = Inputbox("Enter Upper Index Number", "Index-Upper")
then use:
if ((Cells(I,1) >= Startnum) and (Cells(I,1) <= StopNum)) then
Or you can collect the info and split it:
SelRange = InputBox("Enter Range to Hide","Hide")
If (InStr(1, SelRange, "-") <= 0) Then
msgbox "Range must be in format 1-3"
exit sub
end if
NumArray = split(selrange,"-")
and use:
if ((Cells(I,1) >= NumArray(0)) and (Cells(I,1) <= NumArray(1))) then
________________________________
From: Joe Wilson <jhwilson1...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Thursday, April 30, 2009 8:52:04 AM
Subject: $$Excel-Macros$$ Re: Consolidate Macro
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
