Moving cells in a non-contiguous selection

[Bobgoblin]

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.

 

[JLGWhiz]

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.

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]

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]

Thanks for the feedback, Bobgoblin.
Regards, JLG