Excel VBA macro to select range separated by blank row and copy to next empty column

**hackini** · 01-29-2013, 12:50 AM

Hello experts, I have been searching and searching for a solution and I can't get this to work. Like the title says I need a macro to select range separated by blank row and copy to next empty column. I'm really new to VBA so I need some help.

example:

DATE1 TIME1 MISC1
012313 17:03 0.0
012313 17:03 0.0
012313 17:03 0.0

DATE2 TIME2 MISC2
012313 17:03 0.0
012313 17:03 0.0
012313 17:03 0.0

DATE3 TIME3 MISC3
012313 17:03 0.0
012313 17:03 0.0
012313 17:03 0.0

Would like following result:

DATE1 TIME1 MISC1 DATE2 TIME2 MISC2 DATE3 TIME3 MISC3
012313 17:03 0.0 012313 17:03 0.0 012313 17:03 0.0
012313 17:03 0.0 012313 17:03 0.0 012313 17:03 0.0
012313 17:03 0.0 012313 17:03 0.0 012313 17:03 0.0

Basically transpose range to next available column. Is this doable?

Any help will be greatly appreciated...

Thanks

**hackini** · 01-29-2013, 01:12 AM

I was able to copy the range to another sheet but I have no idea how to loop and do all of them and having a hard time copying to the same sheet.

I used:


Sub MoveCellRange()
    e1 = Sheets("Sheet1").Cells(2, 1).End(xlDown).Row
    Sheets("Sheet1").Rows("1:" & e1).Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

Thanks...

**hackini** · 01-29-2013, 01:29 AM

Ok, I tried this is kinda what I need except colums and not rows.

Sub Copy()
    Dim lastRow As Long, i As Long
    Dim CopyRange As Range

    With Sheets("Sheet3")
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = 1 To lastRow
            If Len(Trim(.Range("B" & i).Value)) <> 0 Then
                If CopyRange Is Nothing Then
                    Set CopyRange = .Rows(i)
                Else
                    Set CopyRange = Union(CopyRange, .Rows(i))
                End If
            End If
        Next

        If Not CopyRange Is Nothing Then
            CopyRange.Copy Sheets("Sheet5").Rows(1)
        End If
    End With
End Sub

Thanks again...

**jindon** · 01-29-2013, 01:34 AM

You need to edit your posts and wrap the vba code with the code tag.
It is a MUST rule here.
i.e
[code]
code here
[/code]

See if this is what you wanted

Option Explicit

Sub test()
    Dim myAreas As Areas, i As Long
    Set myAreas = Columns(1).SpecialCells(2).Areas
    For i = 2 To myAreas.Count
        myAreas(i).CurrentRegion.Copy
        With myAreas(1).CurrentRegion
            .Cells(1, .Columns.Count + 1).PasteSpecial
        End With
    Next
    Application.CutCopyMode = False
End Sub

**hackini** · 01-29-2013, 01:52 AM

@jindon
Thanks for your reply and sorry about not wrapping the code.

You are a great help

This is just what I need except I would like to move the data instead of copying it.

Thanks again...

**jindon** · 01-29-2013, 02:02 AM

Thanks for the code tag.

Then use cut instead

Option Explicit

Sub test()
    Dim myAreas As Areas, i As Long
    Set myAreas = Columns(1).SpecialCells(2).Areas
    For i = 2 To myAreas.Count
        With myAreas(1).CurrentRegion
            myAreas(i).CurrentRegion.Cut .Cells(1, .Columns.Count + 1)
        End With
    Next
End Sub

**hackini** · 01-29-2013, 02:13 AM

Thanks jindon
You are a genius. Works perfect. I tried to change copy to cut but kept getting errors.

Thanks again for your prompt reply....

Excel VBA macro to select range separated by blank row and copy to next empty column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Re: Excel VBA macro to select range separated by blank row and copy to next empty column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions