How to get the Last Column number which have data within a specific range ?

[Pramodpandit123]

I wanted to get Multiple Rows & Columns Data and stack it into single column in Excel.
Below is the code i used :

Sub SingleColumn()
r = 3
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 1 To lastrow
    lastcol = Cells(i + 2, Columns.Count).End(xlToLeft).Column 

    For j = 1 To lastcol
        If Cells(i + 2, j + 3).Value <> "" Then
        Cells(i + 2, j + 3).Copy Range("M" & r)
        r = r + 1
        Else
        Exit For
        End If
    Next j
Next i
End Sub

The problem is lastcol give the last cell having the data whereas i want the number of datas within the specific range/Column.
For eg: My datas are within Column D to Column F only. Beyound column F there are unrelated datas.
So i wanted to get number of columns having data within Column D to Column F only.

 

[Anthony47]

Try replacing your line with

lastcol = Application.WorksheetFunction.CountA(Cells(i + 2, "D").Resize(1, 3))

 

[bebo021999]

lc = Range("D3").CurrentRegion.Columns.Count

By the way, using array should help code faster:

Option Explicit
Sub SingleColumn()
Dim i&, j&, k&, rng, arr(1 To 10000, 1 To 1)
rng = Range("D3").CurrentRegion.Value
For i = 1 To UBound(rng)
    For j = 1 To UBound(rng, 2)
        If rng(i, j) <> "" Then
            k = k + 1
            arr(k, 1) = rng(i, j)
        End If
    Next
Next
Range("M3").Resize(k, 1).Value = arr
End Sub

 

[Pramodpandit123]

Wow ! The array seems to work smoothly.But my datas are within Column D to Column F and it also have Headers above the data and at the side of the data as in the attched picture ! So the array code seems to pick the Headers as well and arrange it as shown in output. How could the headers be avoided in output through your code ?

 

[bebo021999]

Actually, Range("D3").CurrentRegion refer to the current region that D3 is in (C2:F7). To refer to D3:F7 only (move down 1 row and right 1 column)
Change

rng = Range("D3").CurrentRegion.Value

to

rng = Range("D3").CurrentRegion.Offset(1, 1).Value

 

[Pramodpandit123]

Thank you sir for the faster alternative !