Hi,

I'm not completely clear what your trying to do but this will select
everything from the first blank cell in Column A to the last cell. If it does
what you want then change .Select to .Delete and you can do it all in one
line.

Range(Range("A" & Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Row), _
Range("A" & Cells(Cells.Rows.Count, "A").End(xlDown).Row)).Select

Mike

"vivi" wrote:

hi

I have a slight problem with my code...any help will be appreciated

I have written some codes and use it for vairous workbooks, however, I must
have written something incorrectly as my the orignal code was written for a
workbook with 853 records, after I have copied the modules to the other
workbooks, they all look fine on Excel, i want to check my copied modules are
working fine and so I press Ctrl+down and went to my last record (26 records)
which is all fine. However, I notice only when I export to Access that the
last record was 853, i.e. it creates 827 blank rows in Access. Therefore I
went back to my Excel fine and press Ctrl+End and found out the record indeed
say my last cell was 853.

I am not sure what I need to do but think if I can write vba to delete all
rows and columns after my last record I should be fine (I have manually done
this and checked Access that it does indeed work!) I am struggle with the vba
code, I tried to use this:

Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

However, it deletes my last record and only column A...what do i need to do???

Please help!! Thanks a lot

P.S. I have only just started and learned vba, please be a bit patient with
me if I sound stupid......

Vivian