CODEWITHSUNDEEP
excelvba
user575219
user575219

Reputation: 2442

Macro without having to select and CtrlC

This macro works only when I select and ctrl C rows from A22-78. I want it to work without me having to do so. This function makes one column out of (#rows)*(#columns in each row)

Sub RowsToColumn()
Dim RN As Range
Dim RI As Range
Dim r As Long
Dim LR As Long
Dim WS As Worksheet
Set WS = Sheets.Add
Application.ScreenUpdating = False

Columns(1).Insert
r = 0
LR = Range("B" & Rows.Count).End(xlUp).Row
For Each RN In Range("B22:B" & LR)
    r = r + 1
    For Each RI In Range(RN, Range("XFD" & RN.Row).End(xlToLeft))
        r = r + 1
        Cells(r, 1) = RI
        RI.Clear
    Next RI
Next RN
Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

Thank u

Upvotes: 0

Views: 86

Answers (1)

Trace
Trace

Reputation: 18889

I tried out your code, and what it appears to do is to read the values from left to right and place them all in column A in respective order. Can this help: 

Sub Test()

Dim oRange          As Range
Dim oSheet          As Excel.Worksheet
Dim vArray()        As Variant

Dim lCnt_A          As Long
Dim iCnt_B          As Integer
Dim lCnt_C          As Long
Dim iCnt_Cols       As Integer


Set oSheet = ThisWorkbook.Sheets(1)

Columns(1).Insert
Set oRange = oSheet.UsedRange
iCnt_Cols = oRange.Columns.Count

vArray = oRange
oRange.ClearContents

For lCnt_A = 1 To UBound(vArray)
    For iCnt_B = 1 To iCnt_Cols
        lCnt_C = lCnt_C + 1
        ThisWorkbook.Sheets(2).Cells(lCnt_C, 1).Value = vArray(lCnt_A, iCnt_B)

    Next iCnt_B
Next lCnt_A

Set oSheet = Nothing
Set oRange = Nothing

End Sub

Please tell me if I got your intentions wrongly.

Upvotes: 1

Related Questions