Ending at a dead cell

[chandler1983]

Greetings!

I am trying to have this Macro export a CSV, works great, however I need it to stop if there is a blank on the A. Can anyone help?

I tried the endXLUP Up, but no avail..

Sub Export()


Dim wsOld As Worksheet
Dim wsNew As Worksheet

Set wsOld = ActiveSheet
lastrow = Range("A2:U1029").End(xlUp).Row
Set wsNew = Workbooks.Add(xlWorksheet).Sheets(1)


wsOld.Range("A2" + CStr(lastrow)).Copy wsNew.Range("A1")


Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:="Z:\SBM\OUTPUT\cs_inv_line.csv", FileFormat:=xlCSV
.Close
End With
Application.DisplayAlerts = True


End Sub

 

[Rick Rothstein]

Maybe...

lastrow = Range("A2").End(xlDown).Row

 

[shg]

Sub Export()
  With Range("A2", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "U"))
    .Select
    Stop ' and take a look
    .ExportAsFixedFormat Type:=xlCSV, Filename:="Z:\SBM\OUTPUT\cs_inv_line.csv"
  End With
End Sub

 

[chandler1983]

Sub Export()
  With Range("A2", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "U"))
    .Select
    Stop ' and take a look
    .ExportAsFixedFormat Type:=xlCSV, Filename:="Z:\SBM\OUTPUT\cs_inv_line.csv"
  End With
End Sub

Hi,

Copy part works perfectly, however I get a run-tire error 5 on this line
.ExportAsFixedFormat Type:=xlCSV, Filename:="Z:\SBM\OUTPUT\cs_inv_line.csv"