P
Pat
Could someone let me know what is wrong with the following two pieces of
code?
Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range
'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A:A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select
Calculate
End Sub
Option Explicit
Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow > 1
LastDataRow = LastDataRow - 1
Loop
End Function
If it isn't clear what I am trying to achieve I will elaborate further.
Thanks
Pat
code?
Private Sub Test_Click()
Dim LastRow As Long
Dim FirstRow As Long
Dim StartRange As Range
'Find end of data in col A in Sheet1
LastRow = LastDataRow(ThisWorkbook.Sheets("Sheet1").Range("A:A"))
With ThisWorkbook.Sheets("Sheet1")
'Find start of data based on reference in Sheet1 A1
FirstRow = Range(.[A1].Formula).Row
Set StartRange = .Range("A1:G1")
End With
StartRange.Resize(LastRow - FirstRow + 1).FillDown
Calculate
ThisWorkbook.Sheets("Sheet1").SaveAs _
Filename:="C:\MyDocumets\Excel examples\ReadyTest.CSV",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Windows("PrepareTest.xls").Activate
Range("D1").Select
Calculate
End Sub
Option Explicit
Function LastDataRow(Col As Range) As Long
'Finds last row containing data in column defined by Col
Dim iCol As Integer
iCol = Col.Column
LastDataRow = Col.Columns(1).Cells(65526).End(xlUp).Row
Do While Cells(LastDataRow, iCol) = 0 Or _
Trim(Cells(LastDataRow, iCol)) = "" _
And LastDataRow > 1
LastDataRow = LastDataRow - 1
Loop
End Function
If it isn't clear what I am trying to achieve I will elaborate further.
Thanks
Pat