how to identify columns - start and end

[david gabra]

hi

hi

I want the below to combine data from columns A to L -

how do I hammend the below to do so?

thanks

-
>>> use code - tags <<<

Sub k()
Dim oneColumnHead As Range
Dim columnHeads As Range

With ThisWorkbook.Sheets("shee")
Set columnHeads = Range(.Cells(2, 8), .Cells(1, .Columns.Count).End(xlToLeft))
End With

For Each oneColumnHead In columnHeads
With oneColumnHead.EntireColumn
With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
.Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value
End With
End With
Next oneColumnHead
End Sub

thaanks

 

[BobBridges]

I don't know whether it's valid to have one With inside another; it isn't clear to me how the VBA interpreter would know what you meant. I'd recommend you assign objects instead, something like this:

For Each oneColumnHead In columnHeads
  Set ocol = oneColumnHead.EntireColumn
  set org = Range(ocol.Cells(1, 1), ocol.Cells(.Rows.Count, 1).End(xlUp))
  org.Parent.Cells(org.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(org.Rows.Count, 1).Value = org.Value
  Next oneColumnHead

But actually that's all pretty complicated. Let's start from scratch: When you say you want to "combine" the data, do you mean you want all the text from all the rows in all the columns A through L to be pasted together into a single cell?

 

[Marc L]

Hi ! With within With really no matters, as always the logic used just must not fail …​

​

 

[BobBridges]

LOL, oh, well, if you don't care how it works then you don't need me; I'll leave you to it.

[Later:] Oops, didn't realize that was Marc's reply; thought it was the OP.

 

[Marc L]

With within WIth is like a loop imbricated into another loop so with a smart logic this is very not a concern …​

​

 

[BobBridges]

So how does Excel interpret a leading dot with a property or method? Does it assume the missing object is the inner or the outer?

Like this:

With ThisWorkbook.Worksheets("Sheet1")
  With Range(.Cells(1, 1), .Cells(5, 5))
    MsgBox .Cells.Count
    End With
  End With

Does it display the number of cells in the worksheet, or in the range? And why?

 

[Marc L]

So it displays the range cells # as it is relative to the closer With codeline, so always the inner like this sample :​

VBA Code to Extract Specific Data Ordered by columns from TXT File into Excel

Hi to Everybody, Have a .txt file that contains specific data that needs to be extracted and placed into corresponding columns in Excel. I'm New to VBA coding so having difficulty in making this work... and I don't Know what to do. In the Excel file I need to import my text file in like this...

chandoo.org

 

[david gabra]

It alway gives me 25

do not see how it counting the items in each column?

 

[Marc L]

As it is obviously the correct expected result …​

​

 

[david gabra]

it does not - sorry
it gives 25 always even if cells are blank
any help will be much appreciated so it only takes those cells that are populated and not blank

 

[Marc L]

According to post #6 sample - which is not in relation with your need just well reading it - this is the correct result.​

Do very not confuse the cells # with blank cells …​

​

 

[rlv01]

I want the below to combine data from columns A to L -

If I make two guesses:

1. You don't say where you want the combined data to go, but the code you posted appends it to column A so I'll use that.​

2. Appending column A data to itself makes no sense, so we will take data from columns B to L and append it to column A.​

(Note that the code you posted in post #1 combines data from Column H to whatever the last column in row 1 which contains data is. But you were VERY SPECIFIC when you said "I want the below to combine data from columns A to L " so I'm taking you at your word. )​

So here is one way you could modify your code.

Sub k1()
    Dim oneColumnHead As Range
    Dim columnHeads As Range
    
    With ThisWorkbook.Sheets("shee")
        Set columnHeads = .Range("B1:L1")             'Quote: "I want the below to combine data from columns A to L"

        For Each oneColumnHead In columnHeads
            With Range(oneColumnHead, .Cells(.Rows.Count, oneColumnHead.Column).End(xlUp))
                 .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value
            End With
        Next oneColumnHead
    End With
End Sub

 

[BobBridges]

To be honest, David, your sample code strikes me as pretty complicated. I expect I could figure it out if I wanted to make the effort, but if I want to count the number of rows used in each column A - L I'd do it this way:

Set owb = ThisWorkbook
Set ows = owb.Sheet("whatever")
Set ocs = ows.Cells
LastRow = ows.Rows.Count
For jc = 1 to 12 'cols A through L
  ru = ocs(LastRow, jc).End(xlUp).Row 'this is the last used row in col <jc>
  Next jc

Now,. what you want to do with the values in each cell is still a mystery to me, but I'd start with this.

 

[david gabra]

i am confused.. sorry
i just waned to countam

If I make two guesses:

1. You don't say where you want the combined data to go, but the code you posted appends it to column A so I'll use that.​

2. Appending column A data to itself makes no sense, so we will take data from columns B to L and append it to column A.​

(Note that the code you posted in post #1 combines data from Column H to whatever the last column in row 1 which contains data is. But you were VERY SPECIFIC when you said "I want the below to combine data from columns A to L " so I'm taking you at your word. )​

So here is one way you could modify your code.

Sub k1()
    Dim oneColumnHead As Range
    Dim columnHeads As Range
    
    With ThisWorkbook.Sheets("shee")
        Set columnHeads = .Range("B1:L1")             'Quote: "I want the below to combine data from columns A to L"

        For Each oneColumnHead In columnHeads
            With Range(oneColumnHead, .Cells(.Rows.Count, oneColumnHead.Column).End(xlUp))
                 .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1).Value = .Value
            End With
        Next oneColumnHead
    End With
End Sub

amazing - does the job and easy to undesttand - great job!!!!