Microsoft Office Forums How to move a selection one cell up using VBA?

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-18-2019, 06:33 PM
Bumba Bumba is offline How to move a selection one cell up using VBA? Windows 7 32bit How to move a selection one cell up using VBA? Office 2007
Novice
How to move a selection one cell up using VBA?
 
Join Date: Jan 2019
Posts: 18
Bumba is on a distinguished road
Default 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")
But I'm getting Run-time error '424': Object required. Also if I remove the Copy method the selection is shown to be C2:C8 and not C3:C8.

Help !?
Attached Images
File Type: png Untitled.png (6.9 KB, 12 views)
Reply With Quote
  #2  
Old 11-19-2019, 07:42 AM
Debaser's Avatar
Debaser Debaser is offline How to move a selection one cell up using VBA? Windows 7 64bit How to move a selection one cell up using VBA? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 170
Debaser is on a distinguished road
Default

You need to resize as well as offset:

Code:
Selection.Offset(-1, 0).Resize(Selection.Rows.Count - 1).Copy Range("E5")
Reply With Quote
  #3  
Old 11-19-2019, 07:57 AM
Bumba Bumba is offline How to move a selection one cell up using VBA? Windows 7 32bit How to move a selection one cell up using VBA? Office 2007
Novice
How to move a selection one cell up using VBA?
 
Join Date: Jan 2019
Posts: 18
Bumba is on a distinguished road
Default

Hi Debaser,

Your code copies data from C2:C7 and not C2:C8.
Reply With Quote
  #4  
Old 11-19-2019, 08:11 AM
Debaser's Avatar
Debaser Debaser is offline How to move a selection one cell up using VBA? Windows 7 64bit How to move a selection one cell up using VBA? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 170
Debaser is on a distinguished road
Default

Sorry, misread your post. You don't actually want to offset the second time then. Did you mean to skip out C9?
Reply With Quote
  #5  
Old 11-19-2019, 08:14 AM
Bumba Bumba is offline How to move a selection one cell up using VBA? Windows 7 32bit How to move a selection one cell up using VBA? Office 2007
Novice
How to move a selection one cell up using VBA?
 
Join Date: Jan 2019
Posts: 18
Bumba is on a distinguished road
Default

Yes, I want to keep the last cell with value in that column out of the range. Hope, you get it now.
Reply With Quote
  #6  
Old 11-19-2019, 08:46 AM
Debaser's Avatar
Debaser Debaser is offline How to move a selection one cell up using VBA? Windows 7 64bit How to move a selection one cell up using VBA? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 170
Debaser is on a distinguished road
Default

Then replace these lines:

Code:
Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(-1, 0).Select.Copy Range("E5")
with this:

Code:
Range(Selection, Selection.End(xlDown).Offset(-1)).Copy Range("E5")
Reply With Quote
  #7  
Old 11-19-2019, 08:50 AM
Bumba Bumba is offline How to move a selection one cell up using VBA? Windows 7 32bit How to move a selection one cell up using VBA? Office 2007
Novice
How to move a selection one cell up using VBA?
 
Join Date: Jan 2019
Posts: 18
Bumba is on a distinguished road
Default

Thanks Debaser. It worked
Reply With Quote
Reply

Tags
vba excel

Thread Tools
Display Modes


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
How to move a selection one cell up using VBA? 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


All times are GMT -7. The time now is 04:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft