Instead of selecting range to copy, find the last row.

[exPERten]

Hello guys.
I have, thanks to searching and asking for your kind suggestions, managed to build a VBA-macro to copy a range of data and create a CSV-file in a specific path.

All fine and dandy this far.
Can I modify my macro to instead specify the first row of valid data (Row 12) and then make the macro find the end of the selection automatically? IE. Last row with data in it.

Existing macro:

Private Sub Create_CSV()
Dim content As String
Dim Rng As Range
Set Rng = Range("A12:AS30")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB As Workbook, _
sWS As Worksheet
Dim dWB As Workbook, _
dWS As Worksheet

Path = "\\PATH\"
FileName1 = Range("A16")
FileName2 = Range("B16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("A12:AS30").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWS.Range("A1").PasteSpecial xlPasteFormats
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV, Local:=True, CreateBackup:=False
dWB.Close False

End Sub

 

[MARK858]

Maybe either

sWS.Range("A12:A" & sWS.Range("A" & Rows.count).End(xlUp).Row).Copy

or

sWS.Range("A12:A" & sWS.Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy

 

[exPERten]

Maybe either

sWS.Range("A12:A" & sWS.Range("A" & Rows.count).End(xlUp).Row).Copy

or

sWS.Range("A12:A" & sWS.Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy

That did just create a CSV-file with the data from column A.

I am not sure I did it correctly, but this is what I did: (Just replaced the row marked with bold fonts.)
Sub Create_CSV()
Dim content As String
Dim Rng As Range
Set Rng = Range("A12:AS30")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB As Workbook, _
sWS As Worksheet

Dim dWB As Workbook, _
dWS As Worksheet

Path = "\\PATH\"
FileName1 = Range("A16")
FileName2 = Range("B16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("A12:A" & sWS.Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWS.Range("A1").PasteSpecial xlPasteFormats
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV, Local:=True, CreateBackup:=False
dWB.Close False
End Sub

 

[MARK858]

My fault...

sWS.Range("A12:S" & sWS.Range("A" & Rows.count).End(xlUp).Row).Copy

or

sWS.Range("A12:S" & sWS.Columns("A:S").Find("*", , xlValues, , xlByRows, xlPrevious).Row).Copy

 

[exPERten]

The last suggestion worked like a charm.
Thanks alot!!!!!!!

Just out of curiosity, what is the difference with your two suggestions?
And is any of them to prefer for any reason?

And I guess my row number 4 is no longer needed? ( Set Rng = Range("A12:AS30") )

 

[MARK858]

You're welcome

And is any of them to prefer for any reason?

The 2nd code is more flexible and uses all the columns to determine the last row, the first code only uses column A to determine the last row. Neither is better than the other, it just depends how you data is set up.

And I guess my row number 4 is no longer needed? ( Set Rng = Range("A12:AS30") )

Wasn't needed in the original code as you weren't using Rng later in the code