Excel Macro to Cut and Transpose cells

Microsoft Excel 2010 - complete product...
September 28, 2017 at 12:44:21
Specs: Windows 10
Hi,

I'm trying to find a macro to cut cells from columns J-P if occupied and transpose them below to column I. For example, If I1, J1, K1, L1, M1, and N1, are all occupied then I need J1-N1 cut and transposed to I2, I3, I4, I5, and I6. If a row only has column I occupied then it should do nothing. Also note, the other columns such as A-H and Q-U may or may not be occupied.

I have workbooks with thousands of rows containing data, so doing this by hand is tedious and I would like to remove as much human error as possible.

I greatly appreciate any help!

Thanks!


See More: Excel Macro to Cut and Transpose cells

Reply ↓  Report •

#1
September 29, 2017 at 01:13:09
Hi,

Something like this will work....


Sub CopyTranspose()
    Dim iEmpty As Boolean

    For i = 9 To 14
        If Cells(1, i) = Empty Then
            iEmpty = True
            Exit For
        End If
    Next i
    
    If Not iEmpty Then
        Range("J1:N1").Copy
        Range("I2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Range("J1:N1").Clear
    End If
    
End Sub


Reply ↓  Report •

#2
September 29, 2017 at 07:19:14
Hi, Thanks for your response.

I should have been more clear in my description. What you wrote does work when the row is set up like I described in the example, but the rows vary. Column I will always be occupied, but the following columns may or may not be. Sometimes it is I1, J1, K1, L1, M1, and N1 like the example, where other times it will be I1, J1, K1, or only I1, and J1 that are occupied. Sometimes it will be only I1 that is occupied and J1-Q1 are empty.

Also, I tried running it with the cells set up exactly how I have the example, but it will work on the first row. If there is another row below after where the cells have been transposed to, it won't do that next row.

Sorry, for my unclear description.

Hope this one makes more sense.

Thanks!

message edited by TRoberts


Reply ↓  Report •
Related Solutions


Ask Question