B
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
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