Lose info upon activate

[Cerberus]

For some reason, I'm having 2 pieces of information deleted as soon as I
select a CommandSub_Click() that only states:

Dim NextRow As Long
Finish.Hide
Worksheets("WORK ORDER").Activate
End Sub

Nothing there says to delete the information to my knowledge. So I tried to
verify the information on the Worksheet_Activate() for Sheet2 (WORK ORDER) to
see if there is anything calling for an unload or delete and it states:

Dim HiddenRow&, RowRange As Range

'< Set the column that contains data >
Const DataCol As String = "A"

'****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = 1 To ActiveSheet.UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(Range(DataCol & HiddenRow).Value) <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

Just to give you an idea of why I'm so confused, the information that is
getting deleted is located in column B and that information is controlled by
a source on Sheet1(CONFIG) which has nothing to do with the first
CommandSub_Click() that was referenced. When tested, the cells that
contained the information that is deleted has the information all the way
until the CommandSub_Click() is preformed.

What is going on here? What am I missing?

 

[Tim Williams]

There's nothing in the code you post which would delete any cell contents.

However, just to remove potential for confusion, I would update your
Worksheet_Activate to make all range references qualified.

Eg:
Change "Activesheet" to "Me"
Use:
Me.Range(...
Me.Rows(...

Any issues there wouldn't explain information being deleted though.


Tim

 

[Cerberus]

Well thank you for that suggestion, might as well do that while I'm in here.
I just wish I knew why the customer's name and sales rep's name get deleted
and all the other 477 cells works perfectly.

 

[Dave Peterson]

When the code is in a General module, the unqualified ranges refer to the
activesheet.

But when the code is behind a worksheet, then those unqualified ranges refer to
the sheet that owns the code.

It's always best to qualify the range no matter where the code is. Then you
don't need to worry. I think that this does what you want:




Dim HiddenRow&, RowRange As Range

'< Set the column that contains data >
Const DataCol As String = "A"

'****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

With Worksheets("WORK ORDER")
For HiddenRow = 1 To .UsedRange.Rows.Count

'Take the Length of the value in column A of each row
If Len(.Range(DataCol & HiddenRow).Value) <> 0 Then
'there's something in this row - don't hide
.Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
.Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow
End With

Application.ScreenUpdating = True

End Sub

You could add Worksheets("WORK ORDER") to each of the ranges/rows:

if len(Worksheets("WORK ORDER").range(datacol...

But the With/End With structure makes for less typing and easier reading!

ps.

This line:
For HiddenRow = 1 To .UsedRange.Rows.Count

May not be processing the rows that you want. The usedrange doesn't have to
start in A1.

If you start a new worksheet and put something in C1720, then the usedrange
will have 2 rows.

I'd use:

Dim LastRow as long
with Worksheets("WORK ORDER").usedrange
lastrow = .rows(.rows.count).row
End with

In my C1720, the .rows.count will be 4, but .rows(4) of that used range will
be row 20.

Same with the first row. It's not always 1.

Dim LastRow as long
Dim FirstRow as long
with Worksheets("WORK ORDER").usedrange
lastrow = .rows(.rows.count).row
FirstRow = .row
End with

But you still may want to start at row 1.

 

[Tim Williams]

Are there any other event procedures, either in other worksheets or at the
workbook level ?

Tim