Moving cells in a non-contiguous selection

Bobgoblin

New Member
Joined
Sep 12, 2014
Messages
2
Hi,

I'm an absolute newbie here and not particularly skilled with Excel.......which is why I'm here I suppose!

I'm hoping someone can help me out. I have a Excel 2007 workbook that contains 30 sheets, each with data for 60 - 90 people on it. The data for each person is spread over two rows and I just need to move the data in one cell for each person down by a row to line up with another piece of their data.

I can use Find/select to select all the cells I want but after a trawl of the web and this forum it seems that I need to use some sort of macro to achieve the move one row down. I have had a blunder around in VBA land without much success and just wonder if anyone here can help me with this please?

The non-contiguous selection would be throughout the workbook and as I said there would be about 60 - 90 cells per sheet selected over 30 sheets, so a bit of a bore to do manually! I'm trying to work with a selection because the cells I need to move down by one row have a unique phrase in them, so Find/select is brilliant at just picking out the cells I need (they don't appear in a regular layout on every sheet so using a row/column general reference wouldn't work).

I've tried to explain myself fully here so I hope it does make sense to those who might be able to help me! Many thanks.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
So long as the string you want to find and move is stand alone (i.e. not embedded in a larger string) then this should work.
Where the data is to be placed on the lower row is not specified, so it is assumed to be directly beneath the existing cell. If not, this code fails.
Code:
Sub dropone()
Dim sh As Worksheet, fLoc As Range, MoveThis As String, fAdr As String
MoveThis = InputBox("Enter the string to move", "TARGET STRING")
    If MoveThis = "" Then
        MsgBox "Invalid Entry"
        Exit Sub
    End If
    For Each sh In ThisWorkbook.Sheets
        Set fLoc = sh.UsedRange.Find(MoveThis, , xlValues, xlWhole, xlByRows, xlPrevious)
            If Not fLoc Is Nothing Then
                fAdr = fLoc.Address
                Do
                    fLoc.Copy fLoc.Offset(1, 0)
                    preAdr = fLoc.Address
                    Set fLoc = sh.UsedRange.FindPrevious(fLoc)
                    Range(preAdr).ClearContents
                Loop While Range(fAdr).Offset(1, 0).Address <> fLoc.Address
            End If
    Next
End Sub
Make a copy of your file to try this on. To install the code, press Alt + F11 and copy the code from this thread into the code window for module1. If the code window is dark when the VB editor opens, click Insert on the menu bar of the VBE, then click module. Save the workbook as a macro enabled workbook. To run the code from the Excel screen, press Alt + F8, then double click the macro name.
How it works. When the macro is initialized it will display an input box asking for the sting to find. Enter the string exactly as it is expected to appear in the worksheet and click OK. If no entry is made, or cancel is clicked, the macro will teminate. If a valid entry is made, the code will begin the search for the string at the bottom of each sheet and will search the entire used range, Each found target will be moved down one cell and the previous location will be cleared. The same procedure will occur for each sheet of the workbook.
 

Bobgoblin

New Member
Joined
Sep 12, 2014
Messages
2
Thanks JLGWhiz, that worked perfectly!

I had a couple of stumbles but that was down to my own mess-ups. Firstly I'd not realised that there were merged cells in the workbook that chucked a spanner in the works a bit, but a short web trawl found me the code to unmerge the entire workbook. Then I realised my omission in my OP - there was unique text that identified all the cells I wanted to move but there was also some more text in those cells that was unique to each and every cell...another spanner in the works!

I worked it out though. The piece of text that was unique and common to all the cells was like "Special id:" (can't say what the text actually was because my company's IG policy prohibits me) then there followed a unique number for each individual in a specific format like "2245 56 345" so when the macro prompted me for the text I just took a chance and tried wildcards in exactly the same layout as the number: "Special id: **** ** ***" It worked a treat! Just thought I'd describe all this in case anyone else struggling with a similar problem reads this thread.

Once again thanks so much JLGWhiz - this and saved me a hell of a lot of work and one hell of a headache!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
Thanks for the feedback, Bobgoblin.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,682
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top