CODEWITHSUNDEEP
excelcsvvbscript
verstappen_doodle
verstappen_doodle

Reputation: 549

Converting all row-column values to column only format

I have an excel sheet. Please look at "Example Correction" sheet.

I have multiple rows and columns with values. I need all the values in column-only format. That is, I need all the values from 2465, 2503 and so on till 331806 in single new column. (Kindly ignore any values after colunm "K" in the sheet).

As there are empty rows intermittently and also unwanted text in the middle areas, I couldn't apply any formula or, VBA script.

Ref: https://www.extendoffice.com/documents/excel/1172-excel-transpose-multiple-columns-into-one-column.html

I tried VBA script, but it works only when all rows are continuously present.

Here's my code:

Sub TableToColumn()
    Dim Rng As Range, LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Set Rng = Range("A" & i, "J" & i) 'Change range to suit needs
        Range("M" & Rows.Count).End(xlUp)(2).Resize(Rng.Count) =     Application.WorksheetFunction.Transpose(Rng)
    Next i
End Sub

This VBA code extract all entries from column A to J, till data exists in rows. How can I apply this into a sheet of entries with more empty rows and unwanted raw text value rows?

Or, via CSV migration and then regex parsing is feasible?

I need the simplest solution for this.

Upvotes: 0

Views: 94

Answers (1)

J Reid
J Reid

Reputation: 461

Okay then let's try this ...

Public Sub Answer()

Dim RowCnt as Integer 
Dim Output as Variant 
Dim Data as Variant
Data = ThisWorkbook.Worksheets("EXAMPLE CORRECTION").Range("B7:K1384")

' How many rows will we have ?
RowCnt=0
for Row = LBound(Data, 1) to UBound(Data, 1)
    for Col = LBound(Data, 2) to UBound(Data, 2)
        if Not IsEmpty(Data(Row, Col)) and IsNumeric(Data(Row, Col)) Then RowCnt=RowCnt+1;
    Next Col
Next Row    
' Resize Output
Redim Output(1 to RowCnt, 1) as Variant
' Fill Output
RowCnt=1
for Row = LBound(Data, 1) to UBound(Data, 1)
    for Col = LBound(Data, 2) to UBound(Data, 2)
        if Not IsEmpty(Data(Row, Col)) and IsNumeric(Data(Row, Col)) Then
            Output(RowCnt, 1) = Data(Row, Col)
            RowCnt=RowCnt+1
        end if
    next
next
' Write to Some Column (Column L) for now
ThisWorkbook.Worksheets("EXAMPLE CORRECTION").Range("L7:L" & RowCnt+6) = Output

End Sub

Upvotes: 1

Related Questions