VBA Assistance needed. trying to select a range of cells, cut, move and paste

Question

I have a spread sheet that is updated weekly. What i need to do is cut come of the cells and paste to a new location. I have never used macros or VBA before but I am getting frustrated with the amount of time I spend doing this. I know that I can use a macro but don't know how to write it.

I am trying to move the name of the hotel and resort to the left of the passengers title

R81C00 CHALET LE VALENTIN SAUZE D'OULX

MR HAYHOE 8

MR GLOVER 2

This repeats throughout the spread sheet. The number of lines between the names is dependent on information further right in the sheet.

546L
__________1 RESORT INFORMATION
__________5 SKI/S.BOARD CARRIAGE
__________8 AD L/P BRN BF 31/12/99

what I would like to do here is move these lines onto the same line as the flight number (this is the same line as passenger details) and then delete the lines with no data. this way all the details would be on the same line and then i would just need to fill down for the hotel names.

thanks in advance for any help please let me know if i haven't explained it clearly.

Answer 1

Trying to keep this general enough to be of use to other people, the basic process to follow would be:

find the next hotel/resort combination
  find each passenger for that hotel/resort
    add in the details for the other attributes
  move on to the next passenger
move on to the next hotel/resort

If we start with finding the hotel/resort combination and we assume that this is on Sheet1 in column A in a single cell and that nothing else is in column A then we would need this macro:

Option Explicit

Sub main()

Dim lngCurrRow As Long
Dim lngMaxRow As Long

With ThisWorkbook.Worksheets("Sheet1")
    lngMaxRow = .UsedRange.Rows.Count

    For lngCurrRow = 1 To lngMaxRow
        If (.Cells(lngCurrRow, 1).Value <> "") Then
            MsgBox .Cells(lngCurrRow, 1).Value
        End If
    Next lngCurrRow
End With

End Sub

This should pop up a message box with the name of each hotel/resort in turn.

All the code does is work out how many used rows there are on the worksheet (and stores that in lngMaxRow) and then works through every used row (using lngCurrRow to keep track of which row we are on) checking the value of the cell in column A on that row (the .Cells(lngCurrRow, 1).Value part). If there is something in that cell (the (<> "" part) then it displays the value of that cell.

The more difficult case is when there is other data in column A (e.g. if the passenger names were also in column A). In that scenario, we need a way to easily recognise what is a hotel/resort combination and what is a passenger name but I don't have enough info about your current structure to determine how to do that