Last used cell problem

[Billy B]

I am using Excel 2003 and have imported and external data to Sheet1. Once the
data is imported, using code the data is sorted then subtotaled by the values
in column D. What is happening is that in the initial import the last cell is
O3534. After the sort and subtotal, the last cell is O3778. Since I am using
a range to do the sort and subtotal and formatting, I want the range to point
to the last cell of any new imported data not the last cell used, which is
O338 from the subtotal list. I have tried deleting all the rows and columns
following the imported data row, the clear contents option but if I use the
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address) code still shows
the last cell is O3778. What I want to do is find the last used cell
containing data based on the last imported data, and delete all other cells
that have been used. Below is the code that is being used.
Thank you.

Private Sub RefreshIt()
'Prompt user for new import data source
With ActiveSheet
Range("O3").Select
ActiveCell.EntireColumn.Delete
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Columns("O").ColumnWidth = "5.57"
Range("A2").Select
End With
End Sub


Private Sub FindSetRange()

Set rngfulldata = Range("A2:" & _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address)
rngfulldata.Name = "FullData"

End Sub


Private Sub TrimIt()
Dim myCell As Range
For Each myCell In Range("FullData")
myCell.Formula = RTrim(myCell.Formula)
Next myCell

End Sub

Private Sub SortIt()

'set display alerts off for now
'turned back on at end of SubTtl procedure

Application.DisplayAlerts = False
Dim rngSortData As Range
Set rngSortData = Range("A1:" & _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address)
rngSortData.Name = "SortData"

Worksheets(1).Range("SortData").Select

Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Private Sub SubTtl()

Range("FullData").Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A94").Select

'Turn the display alerts back on
Application.DisplayAlerts = True

Range("A1:O1").Select
With Selection
.Columns.AutoFit
End With

End Sub

 

[Dan]

Why not use:

Set rngfulldata = Range("A2:" & _
ActiveSheet.Cells.CurrentRegion.Address)

instead of

Set rngfulldata = Range("A2:" & _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address)

Dan

 

[Bob Umlas]

Sub GetRealLastCelAddress()
On Error Resume Next
xc = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column
xr = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
MsgBox Cells(xr, xc).Address
End Sub

Bob Umlas
Excel MVP

 

[Ron de Bruin]

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