| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Visible Cells
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Visible Cells
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 -- Don Guillett SalesAid Software " wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Visible Cells
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 -----Original Message----- 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 -- Don Guillett SalesAid Software " wrote in message ... 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. . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Visible Cells
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 wrote in message ... 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 -----Original Message----- 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 -- Don Guillett SalesAid Software " wrote in message ... 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. . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Visible Cells
Sorry, I had a typo
Range("ae2").Copy Range("ae8") Range("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. -- Don Guillett SalesAid Software " wrote in message ... 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 -----Original Message----- 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 -- Don Guillett SalesAid Software " wrote in message ... 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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying visible merged cells | Excel Discussion (Misc queries) | |||
| Copying and pasting to visible cells only | Excel Discussion (Misc queries) | |||
| problem copying visible cells from 2000 to 2003 | Excel Discussion (Misc queries) | |||
| Copying visible cells only on a protected worksheet | Excel Discussion (Misc queries) | |||
| Help: Copying Visible Cells only to Visible cells! | Excel Programming | |||
Linear Mode
