Where is My Data

[John Quinn]

In the following code the ActiveCell is always empty.

If the computer reads the WorkBook, where is the data in memory?

Option Explicit
Sub TestIntl()

Dim wkst As Worksheet
Dim wslb As Worksheet
Dim lastRow As Long

lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select

Set wkst = ActiveSheet
Do Until ActiveCell.Row > lastRow
MsgBox ActiveCell.Value ' Always Empty
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

lastRow = Range("L" & Rows.Count).End(xlUp).Row
Range("L1").Select

Set wslb = ActiveSheet
Do Until ActiveCell.Row > lastRow
MsgBox ActiveCell.Value ' Always Empty
ActiveCell.Offset(1, 0).Activate ' Move to Next Row
Loop

End Sub

In the example above if I did not have a:

Set wkst = ActiveSheet or
Set wslb = ActiveSheet

This routine works. When I add the two lines above the ActiveCell is empty.

I am trying to understand how Excel works and things like this confuse me.
In VB6 if I create a skelaton file and activate it, there is data in it.

Is this the same concept in Excel?

Thanks For the Help!

John

 

[Chip Pearson]

Your code worked as long as there was some data in column L. If there are
blank cells above the first entry in column L, you'll get an empty result in
the MsgBox. I would recommend that you get rid of ActiveSheet and ActiveCell
and instead reference the range directly. For example,

Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("L1")
Do Until Rng.Row > lastRow
Debug.Print Rng.Value
Set Rng = Rng(2, 1)
Loop

This way there is no ambiguity of what is the ActiveSheet and the
ActiveCell. Moreover, using Select or Activate carries considerable
overhead. It is much more efficient not to Select or Activate anything
unless absolutely necessary.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)