Copying Visible Cells

[rjc44]

In Range C8:E31 I have random integers.

How do I copy JUST the VISIBLE CELLS to Range AE8:AG31 and
sort them in ascending order.

Column C data, copies to Column AE
Column D data, copies to Column AF
Column E data, copies to Column AG

Could anyone help here please.

Thankyou.

 

[Don Guillett]

This should work. On filtered data when you copy you copy all you copy the
visible only. Of course, with the ranges you are using you won't see until
you unfilter.

Sub copysort()
Range("ae2").Copy Range("ae8")
Range("ae8:ag31").Sort Key1:=Range("ae8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

 

[rjc44]

Hi Don,

Thanks for your reply. I don't understand!!!
How did AE2 get into the picture. Do you mean C8:E31.
Either way, the macro just copies any formulas and
formatting from C8:E31 to AE8. Nothing visible.

C8:E31 contains formulas in each cell which produce
numbers (integers) in the range. They can appear in any of
the cells in any sequence, so I can have C8 with 2, C11
with 4, C20 with 6 etc. I want to grab only these numbers
i.e 2,4 and 6 and copy then to AE8 in order. I can't have
2 blank blank 4 etc, because when I then use this data in
another macro, it crashes out because of the blank cells.
(which obviously have data in them as a result of being
copied from a cell with a formula.)

Everything works fine if I just manually type in the 2,4,6
in AE8 down but using a macro to do it fails.

Must be missing something here. Any other thoughts?

Kind regards,
Richard

 

[Tom Ogilvy]

Sub copysort()
Dim cell As Range, rng As Range
Dim rw As Long
rw = 8
For Each cell In Range("C8:C31")
If Len(Trim(cell.Text)) <> 0 Then
Cells(rw, "AE").Resize(1, 3).Value = _
cell.Resize(1, 3).Value
rw = rw + 1
End If
Next
If IsEmpty(Range("AE8")) Then
MsgBox "Nothing found"
Exit Sub
End If
Range("AE8:AG31").Sort Key1:=Range("AE8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

 

[rjc44]

Thanks Tom,
Regards,
Richard

-----Original Message-----
Sub copysort()
Dim cell As Range, rng As Range
Dim rw As Long
rw = 8
For Each cell In Range("C8:C31")
If Len(Trim(cell.Text)) <> 0 Then
Cells(rw, "AE").Resize(1, 3).Value = _
cell.Resize(1, 3).Value
rw = rw + 1
End If
Next
If IsEmpty(Range("AE8")) Then
MsgBox "Nothing found"
Exit Sub
End If
Range("AE8:AG31").Sort Key1:=Range("AE8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

--
Regards,
Tom Ogilvy


(e-mail address removed)


.

 

[Don Guillett]

Sorry, I had a typoRange("c8:e31").Copy Range("ae8")
but I guess I also didn't understand the question. I assumed that visible
cells were cells that had been hidden or filtered as that is what you
indicated.
I see that Tom got clarification and provided your answer.