Trying to delete blank rows after file save
 

In a spreadsheet I pull specific data out and copy into another spreadsheet.
Then, if there are any blank rows I would like to delete them and save the
file again. Here's my example:

Selection.Copy
Workbooks.Open Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcvi.csv"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcviTEST.csv",
FileFormat _
:=xlCSV, CreateBackup:=False

Set r = Range("A2:A200")
Set rr = r.SpecialCells(xlCellTypeBlanks)
rr.EntireRow.Delete


1 6258 40283-6258 0 0 ALD IMMCK
6259 40283-6259 0 0 ALD IMMCK
1 6260 40283-6260 0 0 ALD IMMCK
1 6261 40283-6261 0 0 ALD IMMCK

In this example above i'd like to delete the second row. Once I save the
active workbook I am want to delete the rows using the above Set r......

Trying to delete blank rows after file save
 

So why not add another line like this one -- just change the filename to what
you want.

ActiveWorkbook.SaveAs Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcviTEST.csv",
FileFormat _
:=xlCSV, CreateBackup:=False

pm wrote:

In a spreadsheet I pull specific data out and copy into another spreadsheet.
Then, if there are any blank rows I would like to delete them and save the
file again. Here's my example:

Selection.Copy
Workbooks.Open Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcvi.csv"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcviTEST.csv",
FileFormat _
:=xlCSV, CreateBackup:=False

Set r = Range("A2:A200")
Set rr = r.SpecialCells(xlCellTypeBlanks)
rr.EntireRow.Delete


1 6258 40283-6258 0 0 ALD IMMCK
6259 40283-6259 0 0 ALD IMMCK
1 6260 40283-6260 0 0 ALD IMMCK
1 6261 40283-6261 0 0 ALD IMMCK

In this example above i'd like to delete the second row. Once I save the
active workbook I am want to delete the rows using the above Set r......

--

Dave Peterson

Trying to delete blank rows after file save
 

If your cells are really blank this simple one liner will do
Sub delrowsa()
Range("A2:A200").SpecialCells(xlCellTypeBlanks).En tireRow.Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...

In a spreadsheet I pull specific data out and copy into another
spreadsheet.
Then, if there are any blank rows I would like to delete them and save the
file again. Here's my example:

Selection.Copy
Workbooks.Open Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcvi.csv"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:= _
"X:\Legal to Accounting Check Requests\slsTax dbapcviTEST.csv",
FileFormat _
:=xlCSV, CreateBackup:=False

Set r = Range("A2:A200")
Set rr = r.SpecialCells(xlCellTypeBlanks)
rr.EntireRow.Delete


1 6258 40283-6258 0 0 ALD IMMCK
6259 40283-6259 0 0 ALD IMMCK
1 6260 40283-6260 0 0 ALD IMMCK
1 6261 40283-6261 0 0 ALD IMMCK

In this example above i'd like to delete the second row. Once I save the
active workbook I am want to delete the rows using the above Set r......