You have one line in your code that looks scary to me.

Range("A5:AA209").Cells.SpecialCells(xlCellTypeBla nks).EntireRow.Delete

If you have an empty cell in column A:AA of any row, you want that row
deleted????

It would be more common (to me) to limit that to just a single column. Lots of
databases can have empty cells in non-critical fields.

And one way to clear those cells that look empty (after copy|paste
special|values) but aren't really is to:

select the range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string
replace all

followed by
edit replace
what: $$$$$
with: (leave blank)
replace all

Now those cells that evaluated to ="" are really blank.

In code:


Option Explicit
Sub CMO_Export()

Dim ActWks As Worksheet
Dim newWks As Worksheet
Dim RngToCopy As Range

Set ActWks = ActiveSheet
Set RngToCopy = ActWks.Range("A5:AA209")

Set newWks = Workbooks.Add(1).Worksheets(1)

RngToCopy.Copy

With newWks
.Range("a1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False

.Range("aa:aa").NumberFormat = "yyyy-mm-dd"

.Range("a:a").Replace what:="", _
replacement:="$$$$$", lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False
.Range("a:a").Replace what:="$$$$$", _
replacement:="", lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False

.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
End With
End Sub

I did change your code to only look at column A, too.

Mike G - D.C. wrote:

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

--

Dave Peterson