Hide multiple rows when particular cell is zero

Hi All,

Can anyone tell me how I can hide multiple rows when a particular cell
is zero?
It has to go automaticly, so no buttons...

When D37=0, hide rows 37-48
When D49=0, hide rows 49-58
When D59=0, hide rows 59-68
When D69=0, hide rows 69-78
When D79=0, hide rows 79-88
etcetera.

How can help me with this macro??
Thanks in advanced. 

Best regards, 
BL

0
blommerse (55)
10/19/2006 7:39:28 AM
excel 39879 articles. 2 followers. Follow

17 Replies
554 Views

Similar Articles

[PageSpeed] 45

Hi,

What should happen if e.g. D38 = 0?

Maybe this applied to the desired sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim a, rng As Range

Set rng = Range("D37:D1000")

    For Each a In rng
    
        If a.Value = 0 And a <> "" Then
            Range(a.Row & ":" & a.Offset(11, 0).Row).EntireRow.Hidden = True
        End If
        
    Next a

End Sub

Change rng to suite.
-- 
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"blommerse@saz.nl" wrote:

> Hi All,
> 
> Can anyone tell me how I can hide multiple rows when a particular cell
> is zero?
> It has to go automaticly, so no buttons...
> 
> When D37=0, hide rows 37-48
> When D49=0, hide rows 49-58
> When D59=0, hide rows 59-68
> When D69=0, hide rows 69-78
> When D79=0, hide rows 79-88
> etcetera.
> 
> How can help me with this macro??
> Thanks in advanced. 
> 
> Best regards, 
> BL
> 
> 
0
10/19/2006 9:48:01 AM
Hi BL,
This seems to work...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim I As Long
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D37"
For I = 49 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 37
    Range(Cells(37, 1), Cells(48, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

It's triggered whenever a cell on that sheet changes, so the zero
values governing the row hiding can be determined by a formula in those
D cells or directly entered into those D cells.

If you require that the code be triggered by any workbook calculation
then you could try...

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim I As Long
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D37"
For I = 49 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 37
    Range(Cells(37, 1), Cells(48, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

The trouble here though is if you ever need to manually unhide the
hidden rows to get to a hidden cell to edit its value, the automatic
code just rehides any rows you try to unhide, so  you would need to go
into the VBA editor and disable events in the immediate window.

Ken Johnson

0
KenCJohnson (314)
10/19/2006 11:31:46 AM
Hi Ken,

Thanks, that works perfect!
I have another problem, maybe you can help me also to solve this:
In cell B7 people can fill in 1-10 with validation.
Now what I want is when people fill in 1 only colomn E and F apear.
When people fill in 2 in cell B7colomn E, F, G, H apear
When peple fill in 3 in cell B7 colomn E, F, G, H, I, J apear.
Etcetera.

Colomn A, B, C, D is fixed.
This is not in the same sheet as other one.

Thanks in advanced. 
Best Regards,

0
blommerse (55)
10/20/2006 7:02:45 AM
blommerse@saz.nl wrote:
> Hi Ken,
>
> Thanks, that works perfect!
> I have another problem, maybe you can help me also to solve this:
> In cell B7 people can fill in 1-10 with validation.
> Now what I want is when people fill in 1 only colomn E and F apear.
> When people fill in 2 in cell B7colomn E, F, G, H apear
> When peple fill in 3 in cell B7 colomn E, F, G, H, I, J apear.
> Etcetera.
>
> Colomn A, B, C, D is fixed.
> This is not in the same sheet as other one.
>
> Thanks in advanced.
> Best Regards,

Hi BL,

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7")) Is Nothing Then
Application.ScreenUpdating = False
Range(Cells(1, 5), _
Cells(1, Columns.Count)).EntireColumn.Hidden = False
Range(Cells(1, Range("B7").Value * 2 + 5), _
Cells(1, Columns.Count)).EntireColumn.Hidden = True
End If
End Sub

Ken Johnson

0
KenCJohnson (314)
10/20/2006 7:57:20 AM
Great Ken,

My last question (I hope):
In your code it said:

lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D37"
For I = 49 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 37
    Range(Cells(37, 1), Cells(48, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select

How can I change the code when lookup value changes.
For example when cell D49 changes in D51
And D59 changes in D65. 

Thanks for all your help. 
Greets

0
blommerse (55)
10/20/2006 8:29:04 AM
Great Ken,

My last question (I hope):
In your code it said:

lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D37"
For I = 49 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 37
    Range(Cells(37, 1), Cells(48, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select

How can I change the code when lookup value changes.
For example when cell D49 changes in D51
And D59 changes in D65. 

Thanks for all your help. 
Greets

0
blommerse (55)
10/20/2006 8:29:23 AM
Ken Johnson schreef:

> blommerse@saz.nl wrote:
> > Hi Ken,
> >
> > Thanks, that works perfect!
> > I have another problem, maybe you can help me also to solve this:
> > In cell B7 people can fill in 1-10 with validation.
> > Now what I want is when people fill in 1 only colomn E and F apear.
> > When people fill in 2 in cell B7colomn E, F, G, H apear
> > When peple fill in 3 in cell B7 colomn E, F, G, H, I, J apear.
> > Etcetera.
> >
> > Colomn A, B, C, D is fixed.
> > This is not in the same sheet as other one.
> >
> > Thanks in advanced.
> > Best Regards,
>
> Hi BL,
>
> Try this...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target, Range("B7")) Is Nothing Then
> Application.ScreenUpdating = False
> Range(Cells(1, 5), _
> Cells(1, Columns.Count)).EntireColumn.Hidden = False
> Range(Cells(1, Range("B7").Value * 2 + 5), _
> Cells(1, Columns.Count)).EntireColumn.Hidden = True
> End If
> End Sub
> 
> Ken Johnson

0
blommerse (55)
10/20/2006 8:30:33 AM
Great Ken,

My last question (I hope):
In your code it said:

lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D37"
For I = 49 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 37
    Range(Cells(37, 1), Cells(48, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select

How can I change the code when lookup value changes.
For example when cell D49 changes in D51
And D59 changes in D65. 

Thanks for all your help. 
Greets

0
blommerse (55)
10/20/2006 8:30:55 AM
Hi BL,

> How can I change the code when lookup value changes.
> For example when cell D49 changes in D51
> And D59 changes in D65.

I'm having trouble understanding what you are wanting here.
Could you supply some more information to help me understand your
needs?

Ken Johnson

0
KenCJohnson (314)
10/20/2006 9:32:08 AM
This is what the code do first:

When D37=0, hide rows 37-48
When D49=0, hide rows 49-58
When D59=0, hide rows 59-68
etcetera.

Now I add some rows so the cell which change in 0 (zero) is changed.
So this is what is should do now:

When D43=0, hide rows 43-46
When D57=0, hide rows 57-70
When D71=0, hide rows 71-84
When D85=0, hide rows 85-94
When D95=0, hide rows 95-104
etc.
Till cell D265.

This is what I have:

strCheckAddress = "D43"
For I = 85 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 43
    Range(Cells(43, 1), Cells(85, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select

Regards,

0
blommerse (55)
10/20/2006 10:07:32 AM
Hi BL,

I hope that those changes in row numbers is just a permanent change in
the structure of your worksheet, and that you are not wanting the code
to first work according to your original rules and then start working
according to your new rules. I don't know how the code could be set up
to work one way then change to a different way after you add some extra
rows.

If I am correct in assuming that the change in rows is just a permanent
change you have made to your sheet's structure and that you are simply
wanting the code to work in accordance with that new structure, then
try...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim i As Long
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D43,D57,D71"
For i = 85 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & i
Next i
MsgBox strCheckAddress
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 43
    Range(Cells(43, 1), Cells(46, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case 57, 71
    Range(Cells(rngCell.Row, 1), Cells(rngCell.Row + 13,
1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), Cells(rngCell.Row + 9,
1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

I'm keeping my fingers crossed:-)

Ken Johnson

0
KenCJohnson (314)
10/20/2006 11:44:57 AM
Oops!
Sorry about that MsgBox I forgot to delete. I was using it to check the
progress of the code and neglected to remove it before replying.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim i As Long
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D43,D57,D71"
For i = 85 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & i
Next i
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 43
    Range(Cells(43, 1), Cells(46, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case 57, 71
    Range(Cells(rngCell.Row, 1), Cells(rngCell.Row + 13,
1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), Cells(rngCell.Row + 9,
1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

Ken Johnson

0
KenCJohnson (314)
10/20/2006 11:59:22 AM
I'm getting careless with the line breaks too!


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim i As Long
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D43,D57,D71"
For i = 85 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & i
Next i
MsgBox strCheckAddress
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 43
    Range(Cells(43, 1), _
    Cells(46, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case 57, 71
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 13, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

Hopefully you can cope with my blunders.

Ken Johnson

0
KenCJohnson (314)
10/20/2006 12:06:03 PM
Great, Funny, it works!!! Thanks.
Only I get a message that I'm sure I want to check these cells, you
have to click on OK.
Also not so funny is that it's calculating every time you change
anything.
Maybe you know an anser for it.
Otherwise I want to thank you very much for your time. 

Regards, 
Berry

0
blommerse (55)
10/20/2006 2:08:25 PM
blommerse@saz.nl wrote:
> Great, Funny, it works!!! Thanks.
> Only I get a message that I'm sure I want to check these cells, you
> have to click on OK.
> Also not so funny is that it's calculating every time you change
> anything.
> Maybe you know an anser for it.
> Otherwise I want to thank you very much for your time.
>
> Regards,
> Berry

Hi Berry,

It's just past midnight here and I'm making quite a few mistakes.

I'll try again, this time, no MsgBox and no broken code lines (Google
breaks the code lines if I'm not careful)

Two replies ago I took out the MsgBox, one reply ago I repaired the
broken code lines but carelessly left the MsgBox in:-\

With the MsgBox removed everything should work satisfactorily...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim lLastRow As Long
Dim rngCell As Range
Dim I As Long
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
lLastRow = Range("D" & Range("D:D").Rows.Count).End(xlUp).Row
Dim strCheckAddress As String
strCheckAddress = "D43,D57,D71"
For I = 85 To lLastRow Step 10
strCheckAddress = strCheckAddress & ", D" & I
Next I
Set rngCheck = Range(strCheckAddress)
For Each rngCell In rngCheck
    Select Case rngCell.Row
    Case 43
    Range(Cells(43, 1), _
    Cells(46, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case 57, 71
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 13, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    Case Else
    Range(Cells(rngCell.Row, 1), _
    Cells(rngCell.Row + 9, 1)).EntireRow.Hidden = _
    IIf(rngCell.Value = 0, True, False)
    End Select
Next rngCell
Application.EnableEvents = True
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

Ken Johnson

0
KenCJohnson (314)
10/20/2006 2:25:57 PM
> Hi Berry,
>
> It's just past midnight here and I'm making quite a few mistakes.
>
> I'll try again, this time, no MsgBox and no broken code lines (Google
> breaks the code lines if I'm not careful)
>
> Two replies ago I took out the MsgBox, one reply ago I repaired the
> broken code lines but carelessly left the MsgBox in:-\
>
> With the MsgBox removed everything should work satisfactorily...
>
Hi Ken,

Thank you very very much for all your help.
It works perfect with this code. 
Thanks again. 

Best Regards, Berry

0
blommerse (55)
10/23/2006 8:02:49 AM
Hi Berry,

You're welcome.

Thanks for the feedback.

Ken Johnson

0
KenCJohnson (314)
10/23/2006 10:37:18 AM
Reply:

Similar Artilces:

Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date, StockLocation and NumberOnStock. When I make simple Pivot Table with ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock" I would like to hide rows where the sum is "0". But this does not seem to be possible. Tried to follow the guidelines in the Microsoft article: http://office.microsoft.com/en-us/assistance/HP051998791033.aspx#Hide%20zero%20values%20in%20a%20PivotTable%20report But no luck :-( Mogens You could use conditional formatting on these cells - highlight the cells,...

Pole Display does not update with Multiple Tenders
Hi all, I've searched the board and can't seem to find any hints for resolving for this one. When we tender a transaction the total shows up on the pole display just as it should. If the customer pays part of the total in cash the pole should let them know the remainder, am I right? As it stands now it just displays the total continually. This is primarily a problem as we have a tender set up for "Manufacturer's Coupons" that we use to apply discounts outside our system. Still, I would think that a running total should be available to the customer regardless of wha...

To get the same header,footer and sheet titles across multiple sheets of a workbook.
Dear Friends, I want a workbook to have the same header (as some cell value of a sheet in workbook) and similarly same titles (rows and columns titles) in different sheets. I reached to the following code. It worked well for the header (and also footer) but it didn't work for the rows to repeat at top or columns to repeat. It only works for the active sheet. How can i make my work done?? The main problem i think is to make ActiveSheet.PageSetup.PrintTitleRows work across multiple sheets. Please help me. I have the code attached here for yur reference. 'this is the workbook c...

Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without protecting the sheet. I am making something for other teachers at my school and I want to hide the formulas so they won't mess anything up on the spreadsheet. Any help would be greatly appreciated. Thanks! Hi not without protecting the sheet but why is this a problem for you? -- Regards Frank Kabel Frankfurt, Germany "JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag news:ABFE07BF-6087-4E24-814A-03AF8863A633@microsoft.com... > Hi. I was wondering if there was a way to hide the fo...

Charts in Multiple Sheet in Excel
I am new to the VBA programming & I am trying to develop a VBA script which will create charts (as a separate worksheet) for each worksheet in a workbook. Sub drill() For i = 1 To Sheets.Count Sheets("Sheet(i)").Select Range("A8:I12").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet(i)").Range( _ "A8:I12"), PlotBy:=xlRows ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Oxygen Chart" With ActiveChart .HasTitle = True .ChartTi...

Date-orientated update of cells in different spreadsheets
Dear Excel experts! Hope I am right here... I would like to create a customer-orientated & task daily checklist. Basically, I want to have a single spreadsheet (spreadsheet1) and worksheet (worksheet1) with customer names along the left (1 per row) and tasks along the top (1 per column) which I update on a daily basis. Once the task has been performed for a customer, a value will be written in the appropriate field i.e. for customer1, the task1 result "passed" would be typed into B2, however, for customer2, task1 might have "failed" which would be typed into C...

Data displayed that is not equal to FALSE in a row
In row 1 cells A1-D1 contain formulas (IF/AND statements). In cell E1 I want whatever word that is not equal to “FALSE” to be displayed. A B C D E 1 FALSE FALSE Under FALSE Under The words can appear in any of cells A-D in each row as below. A B C D E 1 FALSE FALSE Under FALSE Under 2 FALSE On FALSE FALSE On 3 Over FALSE FALSE FALSE Over 4 FALSE FALSE FALSE On On Thank you in advance for your help. =SUBSTITUTE(A1&B1&C1&D1,FALSE,"") -- Gary''s Student - gsnu201001 Thanks Gary. That works great! "Gary'...

How do I combine multiple org chart files into one Visio doc?
I want to take 9 individual Visio org chart files and combine them into one file for saving them all together as web page. New to Visio and just need a little help. Thanks. Steve Select everything on a page and then Copy / Paste it to a blank page in your other document. There really isn't a fancier way to do it. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "NC_Steve" <NCSteve@discussions.microsoft.com> wrote in message news:BDE97552-E194-4EA5-B8CE-AEE8DD440D0B@microsoft.com... >I...

Multiple Conditional Formatting rules
Can anyone tell me how I can set 20 rules for conditional formatting o one sheet? What I need to do is colour a spreadsheet with 15-20 colours accordin to a (different) spreadsheet full of numbers, so that eg 1 = red, 2 pink, 3 = blue, 4 = green..... etc... Any help would be much appreciated. Thanks -- Message posted from http://www.ExcelForum.com Hi this can only be done with VBA. Put the following code in your worksheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is No...

Setting Cell Color within a Cell
I'm using quick test professional which uses Excel DataTables, but with half the functionality. I'm basically exporting a the datatable from Mercury quick test to excel. With in my quick test script I'm performing a compare between two excel documents, and I wish to represent the difference with a Red background. Basically, Is they anyway in which I could add so code within my cell that would automatically change the back ground color. Example: My result is 4.13. I would then change this variable within quicktest to something like vbRed.4.13. Then when it gets sen...

HELP: big problem with Excel, source cells, crashing and general madness
I have been on microsoft.public.excel.crashesgpfs but so far just the one reply (from someone who has exactly the same problem as we do and has not yet solved it either). Anyone here can help??? My boss has this massive Excel spreadsheet with a number of worksheets linked with other spreadsheets etc. He is using Excel 2000 on Office 2000. His computer was crashing with the above whenever he tried to do work on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD. We're a small company - a charity - so this was as powerful as we could afford. This was a week ago and it&#...

Multiple SOP Windows
Is there a setting in version 7.5 software setup that allows one user to have more than one SOP window/document open at one time. If not this is simply very poor design. Without this ability it is very cumbersome to handle say periodic customer inquiries and at the same time work at another task such as new order entry. One should simply be able to flip from one window/document to another. -- Best Regards George You can launch another GP session; although, that will use another user license. For inquiries, could a person not use the Sales Document Inquiry to look up information? ...

Printing Shaded Cells
I suddenly can't print out the cells that I have shaded with grey with my black and white printer. It shows it in Print Preview but then it doesn't print the grey. HELP! Usually it will print what shows in preview. Probably related to printer setting? Did you try it before? "Teladria" wrote: > I suddenly can't print out the cells that I have shaded with grey with my > black and white printer. It shows it in Print Preview but then it doesn't > print the grey. HELP! Yes, I have lots of experience with Excel. It turns out I have to shade the cell w...

Automatically Highlight Every Second Row in Excel
Hi, I would like to know if there is any way to have excel automatically highlight every second row in a sheet to make it easier to read accross the row? Thanks GW Can you use conditional formatting? If yes, see Chip Pearson's site: http://www.cpearson.com/excel/banding.htm NHB wrote: > > Hi, > > I would like to know if there is any way to have excel automatically > highlight every second row in a sheet to make it easier to read accross the > row? > > Thanks > GW -- Dave Peterson ec35720@netscape.com ...

How do I stop cells from expanding?
Hey all. Just got Excel 2003, and have a real basic question. I'm working in large files with very long cells, and everytime i click into one to edit it, it expands to take up almost the entire screen. Problem is, i need to look at cells in other columns, and this is a huge pain. How do i get it to stop? Thanks in advance! If it is the formula bar that bothers you can turn it off temporarily under view>formula bar -- Regards, Peo Sjoblom "frodomojo" <frodomojo@discussions.microsoft.com> wrote in message news:FD91C628-9456-454D-9E2A-EF42F6F7F494@microsoft.co...

hide my name
When replying to an email or creating one from scratch how do i hide my name in the "from" field...thx fred <fdbjrgetridofthis@wowway.com> wrote: > When replying to an email or creating one from scratch how do i hide > my name in the "from" field...thx Are you saying you want the reply to be anonymous? -- Brian Tillman [MVP-Outlook] no, I don't mind if my email is revealed.. just my name. I have tried by removing it from the "User Name:" field in the email account but it continues to show up. I have also removed from the header informati...

Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then having it run through a query to filter results. Is there a way that I can have certain feilds hidden until a toggle button or something of the like is clicked? Basically I want have three choices of buttons: Year, Quarter, and Month. What I ideally want to happen is when someone selects the button for year, a hidden drop down menu appears where they can then choose from the available years to filter through the query. I would say I am above average with using access, but I have no idea how to hide fields and then...

How to get rows from 4 diff permitations??
Hi All Wondered if you could help. I have 2 tables of data like so: PUPILS PID PNAME 1 Fred1 2 Fred2 3 Fred3 4 Fred4 LEVELS TID PID YEAR TERMID MATHSLEVEL READLEVEL 1 1 2006 0 W1 W2 2 2 2006 0 1B W4 3 3 2006 0 W1 W2 4 4 2006 0 2C 5A 5 1 2007 1 W1 W2 6 2 2007 ...

Put Active Worksheet's Name in a Cell #3
Is there a way to put the active worksheet's name in the cell? Not the file name of the whole workbook, just the name of the currently selected sheet. Thanks for your help. Hi Corey, There are a few ways this can be done using either macros or functions, here's one of each: *=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1, LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) (sourced from Chip Pearson's site, http://www.cpearson.com/excel/excelF.htm) I recommend checking this page out as it also shows ot...

How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select data > hidden and empty cell button does not give an option to hide gaps. I think by default they are hidden but if you even click on this to see what it does the gaps are shown and no way to turn off. Hi, If you hide the actual rows then with the Plot visible cells only setting on the gaps will be removed. In xl2007 the setting is on the Select Data dialog, Hidden/empty cells button. Cheers Andy Waynesch wrote: > I have a bar chart that is showing gaps and i don't want them. The s...

How to enable auto height with merged cells?
I previously asked a question regarding auto height with merged cells and was given a code to enter into visual basic. I entered the code and copied & pasted a narrative into the row. It originally seemed to work. But after closing the workbook and re-opening it another day the 'referenced' row is back to the original height. Is there a way to for the row height to expand and remain sized so anyone accessing the workbook can read the narrative? Additionall, I need the row to remain at the full height when printing the worksheeet. ...

multiple duplication of records
Could anyone assist me with the following:- I have been using the quick reference search tool that can be found at www.access-programmers.co.uk/forums/showthread.php?t=120366 <http://www.access-programmers.co.uk/forums/showthread.php?t=120366> (cool search tool.zip) Using only one table with no problems however I am now trying to use it in a relationship configuration Two tables linked back to the main table via one too many links. What is now happening is that multiple duplication of records are being displayed. An example If you have one table with two records the listbo...

Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it. Hi is your worksheet protected ? -- Regards Frank Kabel Frankfurt, Germany llong wrote: > I want to hide a worksheet. So, I went to the format menu and the > "worksheet" option is grayed out. Why? And, how do I "ungray" it. I think Frank meant to ask about the Workbook--not the worksheet. (Check under Tools|protection|Protect workbook or Unprotect workbook) My question is: Is there another sheet in the w...

Print multiple non-sequential pages?
Hello, TIA Having been playing around with various printing methods for the past week or so, I'm noticing that I don't see any method to print via VBA a set of pages in a non-sequential order... DoCmd.PrintOut offers us a Range to work with, but how about particular page selections? Ex. Pages 1-4, 9, 15 Is the standard method for this to use PrintOut 3 times, one for 1-4, another for 9, and another for 15? I don't see any reason why I couldn't do this, but was wondering if the feature is built-in somewhere and I haven't found it... thanks ...

Skip multiple blanks
The following details will skip blank cells in a range so that when creating a custom validation list there will be no blanks. BA4:BA500 contain customer names BB4:BB500 contains the formula: =IF(INDIRECT("BA"&ROW(IV4))="","",ROW()) BC4:BC500 contains the formula: =INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4:$BB$500,ROWS($IV$1:IV1)), $BB$4:$BB$500,0)) Defined range with the name SkipCustomerBlanks: =OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500)) ),) When a customer name is deleted in BA the range BC4:BC500 then...