move to end of data in excel sheet - control end

[simonjarvis]

Hi,

I know that the command Control End will move me to the bottom right hand corner of an excel sheet.

However i have a macro that extracts data to fill the sheet so the amount of data in it varies (from 200 rows to 15000 rows) depending on the query.

The problem is that Excel remembers the longest set of data in the sheet so even if your query returned 160 rows pressing Control End would take you to the end of row 15000.

Does anyone know how to reset where excel thinks the end of the data is?

Thanks

Simon

 

[GlennUK]

If you run a macro that examines the usedrange property like this ...

dummy = ActiveSheet.UsedRange.Rows.Count

then it will reset the end of data ( if you really haven't got anything there ).

Glenn.

 

[simonjarvis]

Not sure I understand. I have run this line in a macro, but doing contol end still takes me to row 15000
Simon

 

[GlennUK]

In that case you should delete the unused rows before running the macro.


Glenn.

 

[Nikita6003]

Hiya simonjarvis

Both Usedrange and the ControlEnd option will return the range on the current sheet that has *SOME*thing in it - including just plain formatting and no data.

What I do to get the last row with text or data in is (i./e. ignoring empty but formatted rows)in VBA is this:

ThisWorkbook.Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row

This does and End-ArrowUp starting from cell A65536 - so will move to the last filled cell it can find in column A

If you need to use another column to find the last filled row just substitute the

, 1

with the correct column number

BTW - this code assumes that the data is on "Sheet1" of the workbook that you're running your macro from! If your data is on a different sheet substitute the name "Sheet1" with the correct name

If you need any help in adjusting the code, please let us know

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[KenWright]

If it is a particular Column you want to test how about the following:-

LstRow = Cells(Rows.Count, "A&quot.End(xlUp).Row

Regards
Ken................

 

[GlennUK]

This kind of macro will do what you want ( assumes column A is the column to test ) ...

Sub CleanBlankRows()
Dim CurrentEndOfSheet As Integer, MyEndOfSheet As Integer
CurrentEndOfSheet = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
MyEndOfSheet = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Range("A" & MyEndOfSheet + 1 & ":A" & CurrentEndOfSheet).Rows.EntireRow.Delete
NewEndOfSheet = ActiveSheet.UsedRange.Rows.Count
End Sub


Glenn.

 

[simonjarvis]

Glenn,
That works,
Thanks
Simon