Pasting Values Problem - sorting feature messed up

[Munchkin]

This is complicated to explain - I'll do my best.

I have a macro that copies rows 10-34 from one worksheet & puts them on a
2nd worksheet. Colums A-C of each row contain a formulas that either copies
data from another cell or is left blank. If these cells are blank it means
nothing is entered on any cell of the rows. My macro copies each row anyway,
but only pastes the values onto another worksheet. (There's a reason for my
method, but the explanation would be to lengthy)

The macro works fine at moving the data, but when I try to go in & sort the
copied data I wind up with blank rows at the top. These are obviously the
copied rows that had formulas, but no text - since I pasted the values
nothing is shown in any the blank row cells - no formula or text - so what is
the sorting feature picking up?

 

[JLGWhiz]

You should post the code you are currently using. However, it appears that
the problem is in how you copy the data from the source sheet. You can use
an If...Then statement to ignore the blank rows for copying. For example:

If Range("A1").Value <> "" Then
Range("A1").EntireRow.Copy Sheets(2). _
Range("A" & Range(A65536").End(xlUP).Row)
End If

The above code would only copy the row if there is data in it. Otherwise it
is ignored.

 

[dmoney]

something like this should work

For i = 1 To 50
If ActiveCell.Value = Empty Or ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Activate
ElseIf ActiveCell.Value <> Empty Or ActiveCell.Value <> "" Then
ActiveCell.EntireRow.Copy
''''''paste code here
ActiveCell.Offset(1, 0).Activate
Else
End If
Next i

 

[Munchkin]

So if you copy & skip blank rows it looks for values only in each cell &
ignores the formula?

 

[JLGWhiz]

Yes it will skip the cells where the formula value equates to "". From your
description and since you chose not to post the code you are using, I assumed
that the formulas in the cells are constructed so that if there is no data to
calculte then the value = "" to avoid error flags or zeros from appearing in
the cell.

 

[Munchkin]

Here's my VBA code..but got to be honest, I'm only self-taught @ this stuff.
Not nearly the expert that so many other here are.


ActiveSheet.Unprotect
Sheets("Payment History").Select
ActiveSheet.Unprotect
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWindow.SmallScroll Down:=-9
Rows("2:25").Select
Selection.Insert Shift:=xlDown
Range("A2").Select
Sheets("CR Template").Select
Range("C21:I41").Select
Application.Goto Reference:="NewRecord"
Selection.Copy
Sheets("Payment History").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("B:B").Select
Selection.NumberFormat = "m/d/yy;@"
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2").Select
Sheets("CR Template").Select
Range("D21").Select
ActiveSheet.Protect
End Sub

 

[JLGWhiz]

It appears that you are copying the entire range, including the rows that
have no data displayed, in block and then using paste special to paste only
the rows with values. If you are only working with the 24 rows pasted in
rows 2 - 25 then maybe you could use this little macro to get rid of the
blank rows.

Sub delBlanks()
Dim i as Long
For i = 25 To 2 Step -2
If WorksheetFunction.CountA(Range(i & ":" & i) = 0 Then
Rows(i).Delete
End If
Next
End Sub

This will start on row 25 and work its way to row 2. Since you used
PasteSpecial to paste only values in this range, the CountA should identify
all of the rows that are blank.

 

[JLGWhiz]

Screwed up the step. Use this one.

Sub delBlanks()
Dim i as Long
For i = 25 To 2 Step -1
If WorksheetFunction.CountA(Range(i & ":" & i) = 0 Then
Rows(i).Delete
End If
Next
End Sub