Deleting Blank Lines After PasteSpecial

[___]

Hi All,

I'm having trouble trying to delete blank rows before saving as .txt (please see red code snippet, doesn't work).

Any help much appreciated.

Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim fName As String

Set wbSource = ActiveWorkbook
Set wsSource = ActiveSheet
Set wbDest = Workbooks.Add
    
wsSource.Range("IS10:IV3000").Copy

wbDest.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

wbDest.Worksheets(1).Range("A:A").Select
wbDest.Worksheets(1).Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

wbDest.SaveAs Filename:="C:\Upload\" & Format(Now, "dd-mm-yy hh-mm-ss") & " proforma" & ".txt", FileFormat:=xlText, _
        CreateBackup:=False

 

[Andrew Poulsom]

If it's this line that's causing the trouble:

wbDest.Worksheets(1).Range("A:A").Select

just delete it. You can only select a range on the active sheet, and there is no need to do any selecting to delete the blank rows.

 

[___]

Hi Andrew, Thanks for your quick reply.

I've removed that line of code but it still doesn't save the file without blank lines. It saves the file fine, just with blank lines between the data. Any ideas?

 

[Andrew Poulsom]

I would have thought you would get an error on this line:

wbDest.Worksheets(1).Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

if there were no blank cells in column A.

 

[___]

I've tried changing the code to...

wbDest.Activate
wbDest.Worksheets(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

with no luck.

It saves the file no problem but just seems to ignore the delete lines bit. Is the above code totally wrong?

 

[Andrew Poulsom]

Your original syntax is correct. Unless you have On Error Resume Next somewhere above that line, there must be some blank cells whose rows are deleted, otherwise you would get an error (no cells found).

 

[___]

Yes, I'm getting that error now. It appears that the four columns that have been pasted, A, B, C and D, when opening with note pad and selecting all with a mouse drag, although they appear blank A and B are highlighted blue as if they contain data but C and D seem blank, not highlighted. Any idea what causes this?

 

[___]

Not to worry, I've managed it now.