Code to delete rows not working

[CrankyLemming]

Hi, I hope someone can help with this.

This is probably really obvious, but I've got the following code held
on Sheet 2 of a 3 sheet document. The cells concerned are filled by
data from Sheet 1: =Sheet1!A6, for example. I can't see why it wont
work.

Basically, I want those A-column cells on Sheet 2 which don't hold a
value (their corresponding sheet on Sheet 1 is empty) to hide their
row. I've tried ascribing a value in the 'If cell.Value=' section.
(The cells are formatted for dates. A nil return is giving me
00-Jan-00; I've tried using If cell.Value<01-Jan-00, and 01-Jan-2003)

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Set rng = Range("A6:A21" & Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In Range
If cell.Value = "" Then cell.EntireRow.Hidden = True
Next cell

End Sub

Any answers would be greatly appreciated.

Steve

 

[AlfD]

Hi Steve!

Ask yourself a few questions:

What will trigger the action of hiding rows? Just any change in th
active sheet?

Which sheet is the active sheet? Does Excel know from your code?

When you are working with 2 sheets "on the go" are you being meticulou
in telling Excel which one to treat at that time?

Why define rng and then call it Range?

Come back if you need to

Al

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Dim rng as Range, cell as Range
Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
rng.entireRow.Hidden = False
For Each cell In Range
If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
Next cell
End Sub

 

[AlfD]

Hi Tom!

For Each cell In Range

might better read

For each cell in rng

Al

 

[CrankyLemming]

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Dim rng as Range, cell as Range
Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
rng.entireRow.Hidden = False
For Each cell In Range
If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
Next cell
End Sub

Thanks Tom.

I made a couple of slight changes: ...cell in rng, and If
cell.Value=0, but also amended this to a Worksheet_Calculate() sub.

For my information, is there a short answer to - what's the
difference between the _Change and _Calculate here?

Thanks

Steve

 

[JE McGimpsey]

Calculate fires whenever a cell in the worksheet is calculated. Change
fires whenever a manual entry (or change to an external link) is made.

 

[Tom Ogilvy]

Cell.Value2 is correct. Value2 returns the number stored in the cell, not a
date.

Range("B9").Value = "03/10/2004"
? range("B9").Value
3/10/04
? range("b9").Value2
38056

For Each cell In Range

Range should be rng
(I inherited that from your code - didn't pick it up to correct it)

Calculate fires whenever any action causes the sheet to calculate

Change fires when you edit a cell.

if you want to restrict it to cells that are changed in A6 to the last row

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Dim rng as Range, cell as Range
If target.count >1 then exit sub
Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
if not intersect(target,rng) is nothing then
rng.entireRow.Hidden = False
For Each cell In Range
If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
Next cell
End if
End Sub

 

[Tom Ogilvy]

Alf,
Talk to Steve, that was his code. I guess I didn't pick up all the mistakes.