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

See this article on how to clear the used range:

http://www.contextures.com/xlfaqApp.html#Unused

Mike F
"vivi" wrote in message
...
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

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

Thanks a lot...finally has a solution for my problem :) Have a nice day!!!

"Mike Fogleman" wrote:

See this article on how to clear the used range:

http://www.contextures.com/xlfaqApp.html#Unused

Mike F
"vivi" wrote in message
...
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

See also
http://www.rondebruin.nl/last.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vivi" wrote in message ...
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