Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Hide Zero Rows Macro


  • Please log in to reply

#1
MichaelWayne_71

MichaelWayne_71

    New Member

  • Member
  • Pip
  • 2 posts
Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I have completed my proposal, I would like to click "shrink list" to hide all ROWS that have "0" (zero) as it's value. And then be able to click "expand list" to show all rows again. Essentially, the macro will look only at column A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for less knowledgeable users.

Can someone help?
  • 0

Advertisements


#2
jasonharrod

jasonharrod

    Member

  • Member
  • PipPip
  • 18 posts
Add a autofilter to your column headings.

Macro 1: Then record a macro that "Autofilter's" for Show All
Macro 2: Record a macro that does a custom autofilter.
  • 0

#3
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
Michael,
The response from Jason should work fine.
I have also added a sample VB function as a starting point if you have more complex requirements in the future.


Sub MacHideRows()

'Define Variables
Dim StartRow As Integer
Dim EndRow As Integer
Dim CurrRow As Integer
Dim TestVal As String

StartRow = 1
CurrRow = StartRow
EndRow = ActiveSheet.UsedRange.Rows.Count

'Start
Do Until CurrRow = EndRow
CurrRow = CurrRow + 1
TestVal = Cells(CurrRow, 1).Range("a1").Value

If TestVal = "0" Then
ActiveSheet.Rows(CurrRow).Select
ActiveSheet.Rows(CurrRow).RowHeight = 0
End If

Loop
Range("A1").Select
dummy = MsgBox("Finished", vbOKOnly)

End Sub



HTH
David
  • 0

#4
MichaelWayne_71

MichaelWayne_71

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
Thank you guys...awesome!
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP