#1
|
|||
|
|||
How to move a selection one cell up using VBA?
I'm trying to remove the last cell from a selection dynamically and copy the range. The worksheet looks like the attached image. I'm trying to select and copy the range C3:C8 dynamically using VBA. I've tried the below code: Code:
Cells.Find(What:="id", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.Offset(-1, 0).Select.Copy Range("E5") Help !? |
#2
|
||||
|
||||
You need to resize as well as offset:
Code:
Selection.Offset(-1, 0).Resize(Selection.Rows.Count - 1).Copy Range("E5") |
#3
|
|||
|
|||
Hi Debaser,
Your code copies data from C2:C7 and not C2:C8. |
#4
|
||||
|
||||
Sorry, misread your post. You don't actually want to offset the second time then. Did you mean to skip out C9?
|
#5
|
|||
|
|||
Yes, I want to keep the last cell with value in that column out of the range. Hope, you get it now.
|
#6
|
||||
|
||||
Then replace these lines:
Code:
Range(Selection, Selection.End(xlDown)).Select Selection.Offset(-1, 0).Select.Copy Range("E5") Code:
Range(Selection, Selection.End(xlDown).Offset(-1)).Copy Range("E5") |
#7
|
|||
|
|||
Thanks Debaser. It worked
|
Tags |
vba excel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find last non empty cell (moving horizontally), then move one cell to the right | klutch | Word VBA | 9 | 07-09-2018 06:16 PM |
Move Selection to the next Column on a Page (Not a table) | GuitarForLife | Word VBA | 7 | 02-09-2018 01:59 PM |
extract time from a cell and move it to front of cell before text | ewso | Excel | 20 | 03-19-2017 11:34 AM |
Move the data at bottom cell to right side of the cell | kcyag91 | Excel | 1 | 01-28-2016 12:28 AM |
Move data from 1 cell to another cell | Catalin.B | Excel | 1 | 06-25-2011 12:51 PM |