how to delete the rows below the last cell that contains data

[santosh1]

How can I delete the rows below the last cell that contains data. I am not too familiar with excel VBA.

I am doing the following in my Access VBA currently to import the excel data into Access table.
Thankyou.

Dim exApp As Excel.Application
Dim ws As Excel.Worksheet
On Error GoTo errExportToExcel

Set exApp = CreateObject("Excel.Application&quot
exApp.Workbooks.Open "C:\Hospital.xls"
exApp.Application.Visible = True
exApp.Application.ActiveWorkbook.Activate
Set ws = exApp.Worksheets("UPLOAD&quot
GoToLastCell ws

' Import the spreadsheet file
DoCmd.TransferSpreadsheet acImport, nSpreadSheetType, "SPECTRUM_CALCULATION", _
cFileName, Me!ChkBox_FieldNames
Set exApp = Nothing

errExportToExcel:
Select Case Err.Number
Case 3010
MsgBox "Hospital.xls file is already open. Close file and try again.", vbOKOnly

Exit Sub
Case Else
MsgBox Err.Description, vbOKOnly
DoCmd.Hourglass False
Exit Sub
End Select

Sub GoToLastCell(ws As Worksheet)
ws.Select
If Range("A1&quot.SpecialCells(xlLastCell).Value = "" Then
Cells(Cells.Find("*", _
ActiveCell.SpecialCells(xlLastCell), _
, , xlByRows, xlPrevious).Row, _
Cells.Find("*", _
ActiveCell.SpecialCells(xlLastCell), _
, , xlByColumns, xlPrevious).Column).Select
Else
Range("A1&quot.SpecialCells(xlLastCell).Select
End If

End Sub

 

[xlbo]

to find the last cell with data, use lastRow = Range("A65536&quot.end(xlup).row

This will give you the last row of data

however - you can't delete the blank rows at the end of an excel workbook - they'll just get replaced by more blank rows
HTH
Geoff

 

[larryww]

xlbo - want to reconsider that response?

 

[larryww]

While I'd like to think that Lotus's "minimum recalc" talents of the 1980s - whoops! Strike that! Time warp! Gates doesn't allow optimization; it's anti-Wintel!

Before answering, I would say that it's bold to take a language you're unfamiliar with and embark on a deleting crusade

The issue of last cell is not exactly trivial. j-walk.com even sells a utility to sort of "fix" the last cell on a sheet.

xlbo: Suffice it to say that certain conditions -- probably ranges and { [columnar]- or [Select All]- } formats, or whole-sheet-imports -- can cause the Excel-perceived "last cell" to be as bad as IV65536; and this can astoundingly bloat file size - yes, even in Excel 2000. Deleting those essentially useless lines and columns does matter; and can change the Excel-perceived last cell.

You can learn more about the last cell at

http://www.ozgrid.com/VBA/ExcelRanges.htm

FWIW I consider John Walkenbach to be the infallible authority on anything in Excel coding, so check out

http://www.j-walk.com/ss/excel/eee/eee011.txt

Now, this may all be noise to you, since you obviously came upon some slick code already from what I see. So how to delete? The actual command is Selection.EntireRow.Delete
(after selecting them, of course). Selection.Clear would (I believe) have the xlbo effect - not really deleting them.

I may be offline for a while so anyone pitch in if there are more questions.