Anyone a closet Excel expert?

A place for us all come together to discuss general topics of interest. Want to chat about music, movies, news, real life, or a game that we don't have a group for? This is the place.

Moderators: General Forum Moderators, Global Moderators

Anyone a closet Excel expert?

PostPosted: Tue Nov 21, 2006 9:52 pm

User avatar
NF
Arbiter
Posts: 751
Joined: Wed Sep 01, 2004 6:51 pm
Location: Manchester, UK
Skype: philjcollinson1
I'm doing a spreadsheet for work and I was wondering if it is possible to use formula to hide rows/cells

For instance if you have this:

***********
Sheet1

CellA: Y
CellB: Y
CellC: Y
***********
Sheet2

Cell1: Line 1
Cell2: Line 2
Cell3: Line 3
***********

And then you change one entry:
i.e.

***********
Sheet1

CellA: Y
CellB: N
CellC: Y

***********

and then on Sheet2: Line2 is then hidden so that cells below are bumped up to keep the list complete.

***********
Sheet2

Cell1: Line 1
Cell3: Line 3
***********

Hope this makes sense and people can see what I'm getting at. Anyone know if this is possible?
NF

PostPosted: Tue Nov 21, 2006 10:15 pm

User avatar
Isileth
Posts: 1011
Joined: Wed May 25, 2005 4:45 pm
Location: UK
Sub HURows()
BeginRow = 1
EndRow = 3
ChkCol = 1

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = N Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

That should do the trick. Not to sure how to make it run off another sheet however.

PostPosted: Tue Nov 21, 2006 10:28 pm

User avatar
Zepol
Posts: 273
Joined: Sun Nov 05, 2006 5:00 pm
Yahoo Messenger: z3p0l@yahoo.com
Location: Texas
Isileth wrote:Sub HURows()
BeginRow = 1
EndRow = 3
ChkCol = 1

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = N Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

That should do the trick. Not to sure how to make it run off another sheet however.
yep shes got it
(RIP-You will never be forgotten)Zepol lvl 70 Lock- Afflication and Destruction.
(Reborn)Zepol lvl 80 Mage - Arcane (Akama)
Ive lvl 80 Shamman - Resto/Elemental(Akama)
Faeith lvl 80 DeathKnight - Frost Tank/Blood DPS(Akama)
Never give up before attempting it first.

PostPosted: Tue Nov 21, 2006 10:33 pm

User avatar
Isileth
Posts: 1011
Joined: Wed May 25, 2005 4:45 pm
Location: UK
He...but then I do have great hair so it can be confusing :)

PostPosted: Wed Nov 22, 2006 1:29 am

User avatar
NF
Arbiter
Posts: 751
Joined: Wed Sep 01, 2004 6:51 pm
Location: Manchester, UK
Skype: philjcollinson1
Wow you da man! (think we established that anyway ;))

Thankyou sooo much for that, was really struggling and you've just saved me soooo much hair pulling!!

Once again you're a god.
NF

Return to “General Discussions”