Cells(Rows.Count, 1).End(xlUp).Rows returns zero unless I add a new field

[KevH]

Howdy all,

I have a worksheet with log data that was pushed from Access into Excel. I have a single Data/Time field and a few number fields. I'm trying to select the last record (to use in a range) using the formula below.

i = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Rows

However, If I use any of the existing fields, the result is always zero. If I insert a new field and add some records to it, I get that field's last row as you'd expect.

What am I missing here?

Thanks,
Kev.

 

[lenze]

Remove the "s"

i = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row

lenze

 

[KevH]

That doesn't affect the behavior.

 

[lenze]

What are you trying to do??? POst your full code!!!
The 1 in your code limits the macro to only column "A".

lenze

 

[KevH]

Here's the code so far.

Dim Range1 As Range
Dim Range2 As Range
Dim i As Integer
Dim TargetSheet As Worksheet
'---------------------
Set TargetSheet = Worksheets("Data")
i = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Range1 = TargetSheet.Cells(4, 1) 'move past three header rows
Set Range1 = Range1.Resize(i - Range1.Row + 1, 1)
'---------------------
For Each Range2 In Range1.Cells
'Do some stuff
Next Range2

 

[KevH]

Also,

I'm aware that the 1 is the column indicator. I've tried adjusting it with and without the new row and in each case only the new row returned something other than zero.

Right now I'm only trying to select a range for the For Each loop. I've got this working in other WB's, but for some reason not in this one.

I thought it might have been a file type issue, but XlS, XLSX, and XLSM are all doing this.

 

[poolhall]

i = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Rows

However, If I use any of the existing fields, the result is always zero.

I don't think it can be zero, should be at least 1.

 

[KevH]

Maybe this will help.

In the old field:
- If I leave the data at its current row (49422), then it returns zero.
- If I cut the data (to say 10 rows) it STILL returns zero.

In the new field:
- If I fill data to the bottom row (49422), then it starts returning zero too.
- If I undo that (and cut the data back down 10 rows) it goes back to working correctly.