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
766 Views

Similar Articles

[PageSpeed] 29

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:

Why does money 2005 have multiple categories for the same thing?And why does it keep spliting the food categories apart on me?
Take rent for example they have it in 2 places. On a side not why is food no longer have subcategories. The stupid restore categories breaks the food categories back apart even when explicitly place the subcategories before I do that. Its annoying!!!!!!!!!!!!!!!!! Yes it is. I'm not sure what all of the reasons are for this, but trying to eliminate subcategories to support the "Essential" "Less Typing" "Effortless way" seems part of the answer. Why it had to mangle existing data to do this is still a mystery. See also http://umpmfaq.info/faqdb.php?q...

Hide path in macro warning box
hey guys! Is there a way to hide the path to my file when the macro warning bo pops up? Thanks! : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com I don't think so, but maybe you could mask it. Provide two workbooks. The first workbook's sole purpose is to open the "real" one. But put the first workbook somewhere that you can publicize. The user will get see that drive/folder. And if they allow macros to be run, then the other will open with no veri...

Search in a multiple-page document
Hello, I have a document with several pages, un per year, and in each page a table -lines and columns with data-. The first column in each page is a list of names, and in each list a given name may be repeated several times. I need to prepare a "search" able to draw to a new page all the information in the same line of the searched name, as many times as that name appears in each of the document pages. In other words, the search must admit several results for one searched item and copy them in a blank page. Is it possible? ...

delete blank rows #3
I have an excel spreadsheet sent to me that contains blank rows. Is there a way to get rid of the blank rows with a formula so I don't have to go through and delete. There are over 2000 rows with blanks inbetween each row of text. Thanks, If you set up Autofilters then you can filter one column for (blanks), then highlight all the blank rows which are displayed (row identifier turns blue) and Edit | Delete Row in one operation. Select (All) from the filter pull-down list and all your blank rows will have gone. Hope this helps. Pete ...

Two Formats for One Cell
Is it possible to have two formats in one cell. I have a column in which dates or decimal points will need to be keyed (ex. 20070301 or 0.00)? -- Ivy One way: Format/Cells/Number/Custom [>=19000000]00000000;[<19000000]0.00;General In article <5DB28F0B-9684-478F-9570-04835F036DA7@microsoft.com>, Ivy <ivy@discussions.microsoft.com> wrote: > Is it possible to have two formats in one cell. I have a column in which > dates or decimal points will need to be keyed (ex. 20070301 or 0.00)? > > > > -- > Ivy ...

working with multiple worksheets #2
I would like to collect data from one specific cell (cell E3) in 90 worksheets and have this data placed in one column from 1-90 on one single worksheet. Is there a way to do this without doing it one by one? Hi! Are you using the default sheet names? (probably not, huh?) If there is some common sequence in the sheet names then a formula could probably be crafted to do this. Biff "Shaun" <Shaun@discussions.microsoft.com> wrote in message news:B8CEFE1A-1B25-41F9-B72F-103CB6AF476D@microsoft.com... >I would like to collect data from one specific cell (cell E3) in 90 &g...

Keyboard shortcuts & resetting ruler zero points
I recently moved to Visio '07 from 2000, and I find that many of the keyboard shortcuts available in the older version appear to be missing. For instance F8 used to call up the Align Shapes dialog. Are they no longer used, or might there be an "add-on" to the app that has the shortcuts or allows them to be set? Also, is there a way to reset the zero points on the rulers or are these points fixed? Thanks -- Ron On Tue, 24 Mar 2009 07:35:03 -0700, RonDay <RonDay@discussions.microsoft.com> wrote: >I recently moved to Visio '07 from 2000, and I find that many o...

Paste Data into Contiguous (Visible) Cells
I am trying to paste data from a set of contiguous cells in one sheet into some cells in anther sheet. I have hidden the columns I do not want to paste the data so that the selection that I would want to paste into looks contiguous. I was hoping that Excel would see it that way also and just paste one cell after another. However, it chooses to paste to the hidden cells, which is not what I want. Does anyone know how (without writing a macro) I can paste into cells that are not contiguous normally, but appear contiguous when some columns are hidden? Thanks in advance. Trevor -- trev_s...

How do i keep cells with a formula returning "" from being plotted
I have a chart that referrences cells that contain an IF statement that returns "". Although the chart option 'do not plot blank cells' is marked, Excel graphs these cells as being 0. Hi, Instead of "" use NA() Cheers Andy TBB wrote: > I have a chart that referrences cells that contain an IF statement that > returns "". Although the chart option 'do not plot blank cells' is marked, > Excel graphs these cells as being 0. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info unfortunately "" will plot as a zero...

Date and Time in a cell
Hello -- I have a cell formatted as date and time. Is there a way to extract just the time portion of that cell to another cell? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ sure. Just format it as time. "fluff" <fluff.whm4b@excelforum-nospam.com> wrote in message news:fluff.whm4b@excelforum-nospam.com... > > Hello -- > > I have a cell formatted as date and time. Is there a way to extract > just the time portion of that cell...

returning blank cells
i know this must be simple.....but......im referencing one sheet to another....example on sheet 2 =(sheet1!a1) ...i want the cell to be blank on sheet 2 unless there is information on sheet 1......i know this isn't brain surgery...but ive tried all the cell formats and nothing seems to return a blank on sheet 2....HELP Thanx, Mike Hi! You don't need the parenths in that formula: =Sheet1!A1 You could use this to return a formula blank: =IF(Sheet1!A1="","",Sheet1!A1) OR, use this custom format to suppress the display of 0's: 0;-0;;@ OR, goto Tools>O...

Word Doc saves as multiple PDF files
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have many Word docs that each have multiple sections, and when I use the Print dialog box to save these documents as PDFs, I end up with a separate PDF for each of the sections. Is there any way to avoid getting multiple PDF's without having to create my Word doc as one single section? Not as yet, but you can combine the output into a single PDF using Apple's Preview app or any of a number of PDF utilities as well as Acrobat. With Preview it's just a matter of opening one of the PDFs, expandi...

Online services -multiple accounts
I am using MS Money Plus Premium. I cannot find out how to add multiple accounts for online updating. I have my checking at one bank, cards at multiple other banks and car loans a yet another financial institution. How do I setup online services to update all these accounts. I wasn only given the option to setup one bank. Period. Please help. In microsoft.public.money, Kane wrote: >I am using MS Money Plus Premium. I cannot find out how to add multiple >accounts for online updating. I have my checking at one bank, cards at >multiple other banks and car loans a yet anothe...

Writing data to Specific Cells???
Hi Folks, I'm trying to write data from cell calculation which updates automatically as part of a FOR loop into a specific range of cells. I would like to be able to read in a numeric value from a cell and loop round that number writing to cells M1 to M100 or so but all I can get is the data writing accross the row rather than down the col??? Here's what I have so far! Any Help Appreciated Private Sub CommandButton1_Click() Dim myRange As Range Dim myrange2 As Range Set myRange = Worksheets("Sheet1").Range("A110") Set myrange2 = Worksheets("Shee...

logging date & time of data entry for individual cell
I'd like to log the time & date that content was entered into a (formerly empty) cell. When any data is manually entered into cell A1, the then-current time & date goes into cell B1 (and henceforth doesn't change). When data is entered into A2, the then-current time & date goes into cell B2. Once data has been entered into a cell, it will not be edited again. Can anyone suggest the best way of doing this? Thanks! Julz Put the following macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") If Intersec...

1 Computer multiple outlook users
Would like to use outlook for myself, wife, and kids. How can I setup each of us on a single home pc (office 2002 with windows xp)? We all of course will share the same ISP access with unique email accounts from this service. Your help is appreciated. Guy Set up a User for each person and you will automatically get a different instance of Outlook for each User. "Guy Huus" <ghuus@nemontel.net> wrote in message news:0ae901c3a868$3891a4c0$a001280a@phx.gbl... > Would like to use outlook for myself, wife, and kids. > How can I setup each of us on a single home pc (...

Outlook 2003 acquiring multiple instances of a message store
Hi, I've been working on a project involving a MAPI message store provider and I've noticed some peculiar behaviour with Outlook 2003. Specifically it seems that Outlook invokes the MSProviderInit() function on three separate occasions, and I'd like to understand why. The first two invocations occur directly after I access the message store for the first time. The third occurs some (indeterminate) time later (normally between 15 and 20 seconds after Outlook is launched). What on earth is it doing? Why does Outlook 2003 require three separate instances of a message store. This fun...

Printing multiple report based on a list
\Hi Folks, I create a workbook for a benchmark test that will allow me to target specific items where a student may need to improve. But at the end, I create a report with all the questions and corresponding student's choices for those qestions. Every time you select a child name you have a report for that child. My question is how to print the report for the entire class so every child can see his/her performance on the test? -- Ti Moy I don't know how many students and questions You could setup a different worksheet for each student Do the first perfectly ulilizing ...

Cell to show last day of month
Hi, I've created a macro that automatically copies from an existing worksheet and insert a new sheet with the copied data. For a particular cell in the new sheet, I'd like the new sheet to show the date of the last day of a subsequent month, ie, in the existing sheet, the cell shows 30/4/04. In the newly inserted sheet, I'd like it to show 31/5/04 on running the macro. What code should I include in my macro for this to happen? Thanks! Val Val, I couldn't find a handy end-of-month function in VBA, and Eomonth isn't supported in Application.WorksheetFunction. So...

"search" a different sheet with multiple criteria
I would like to know if its possible to use a formula like VLOOKUP using more than one criteria in Excel 2007? For example, say I have Sheet 1 that includes A :: B :: C :: D BUS 280 :: SU2009 :: online :: Art BUS 340 :: FA2009 :: online :: Mike MGT 240 :: FA2009 :: hybrid :: Steve MKG 344 :: SP2010 :: online :: Terrance MKG 401 :: SU2009 :: online :: Art On Sheet 2, I want to have three drop down boxes in A1, B1, and C1, so the user can select the criteria on which to search. (A1---Choose a course ID, B1---Choose a first run date,...

Copy Paste of Filtered Data "Uses" too many Cells
Using XL 2003 & 97 Have about 8000 rows of data in 18 Columns. Using Autofilter, I filtered the data down to about 5000 cells After inserting a new sheet, and using VBA, I copy/pasted the filtered data to the New Worksheet. Then I noticed that the New WS had a used range of >65,000 rows by 18 columns. Of course this is not efficient. What is causing the excess cell "Used Range"? The code that I am using to copy paste follows: ......... Sheets.Add.Name = "New" Sheets("Filtered Data").Activate Cells.Copy Sheets("New").Act...

grouping rows...
say: DEPT1 EMP1 DEPT1 EMP2 DEPT1 EMP3 DEPT2 EMP4 DEPT2 EMP5 DEPT3 EMP6 DEPT3 EMP7 .... .... .... if I want to group the rows by dept, do I have to select dept1 and group them, and select dept2 and group... for every dept? any better ideas? Data | Filter | Autofilter is probably the fastest way to simply look at rows with specific DEPT# entries in them. If you've got some special need to do much more than that with them, then we'd need a more detailed explanation of what you want to do with this information. "kang" wrote: > say: > DEPT1 ...

If adjancent cell contains text then ignore, otherwise copy and paste multiple values in one column to another column
Hi all I currently have a column (say column A) that contains a list of numbers.= =20 In column B, in every 15th row, e.g. row 15, 30, 45 etc, there may or may n= ot be a comment. Where there is a comment, this nullifies the 15 rows of nu= mbers in column A, e.g. if there is a comment in B30, the numbers between A= 30 and A44 are nullified. The comments may be one or two words but vary eno= rmously so I cannot say it will be either this word or that word.=20 Where there are no comments in column B, I would like to copy and paste the= 15 corresponding numbers in column A into column C. For ...

How do I create a macro to duplicate the current row?
I want to create a macro that will insert a blank row beneath the current row and will then copy the contents of the current row to the inserted row. I can do the operation manually but when I record it as a macro then run the macro it is not quite right. It works fine if I want to add just one new row. However, when I need run the macro to add further rows the macro always returns me to the point (row) where the I ran it for the first time. Does anyone know what I need to put into the macro, when editing it, to stop this happening? Hi As always post your macro for co...

How to run a Macro from a cell formula
I'm trying to cause a macro to run based on the value of a cell. Can anybody help me? See answer to your later post. In article <006001c3a55e$326ed8e0$a501280a@phx.gbl>, "Vic" <anonymous@discussions.microsoft.com> wrote: > I'm trying to cause a macro to run based on the value of a > cell. Can anybody help me? ...