G
Guest
I have a macro that selects a range of cells, A1:S21000, and copies data to a
new workbook, which is then saved as a .txt file. Is there a way to select
the range and only copy cells that have true values (not formulas)?
The source sheet contains formulas from row 1 to row 21000 to accommodate
for potential data input. In most cases, however, there will be many empty
rows. As my code is now, the entire range is copied to the new sheet,
including blank rows. Is there any way to easily eliminate these blank rows
from either the copied range before paste or from the destination sheet?
Incidentally, I tried selecting all of the blank rows from my destination
sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
active cell still shows as S21000. What gives?
Any help would be greatly appreciated.
Mike
CODE:
Sub CMO_Export()
Range("A5:AA209").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=False
Columns("AA:AA").Select
Application.CutCopyMode = False
Selection.NumberFormat = "yyyy-mm-dd"
Range("A5:AA209").Cells
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
new workbook, which is then saved as a .txt file. Is there a way to select
the range and only copy cells that have true values (not formulas)?
The source sheet contains formulas from row 1 to row 21000 to accommodate
for potential data input. In most cases, however, there will be many empty
rows. As my code is now, the entire range is copied to the new sheet,
including blank rows. Is there any way to easily eliminate these blank rows
from either the copied range before paste or from the destination sheet?
Incidentally, I tried selecting all of the blank rows from my destination
sheet, then delete-entire row. Immediately after, I hit ctrl+end and the
active cell still shows as S21000. What gives?
Any help would be greatly appreciated.
Mike
CODE:
Sub CMO_Export()
Range("A5:AA209").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=False
Columns("AA:AA").Select
Application.CutCopyMode = False
Selection.NumberFormat = "yyyy-mm-dd"
Range("A5:AA209").Cells
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub