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 Refre****()
'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

Why not use:

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

instead of

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

Dan

"Billy B" wrote:

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 Refre****()
'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

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

"Dan" wrote in message
...
Why not use:

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

instead of

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

Dan

"Billy B" wrote:

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 Refre****()
'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

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

--

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




"Billy B" wrote in message ...
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 Refre****()
'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