# Combining columns by finding last used row in a column

1. ## Combining columns by finding last used row in a column

Greetings folks !

I've been bashing my head against the wall for awhile on this one. Here's what I'm trying to do :

Sheet1 = Several columns of time stamps
- The user clicks a check box to determine if they want the include this column in a data analysis
- The user presses an 'Analyze' button and it copies the data to a new column in sheet2 where I can do all kinds of wonderful math against it
- Each individual column is then put together in one column on Sheet2 and sorted

The last step is where I'm stuck. The data will need to start in cell A40. Cell A39 is populated by a cell with the string "Sorted Data" in it. My sort algorithm works, but I can't get the data properly into one column starting in A40. Here's the function I'm using for one of the columns :

If Sheet1.[include1].Value = 1 Then
Sheet2.Range("A1") = Sheet1.Range("B5")
Sheet1.Range("B6", "B40").Copy
Sheet2.Range("A2", "A36").PasteSpecial Paste:=xlPasteValues
Sheet2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If

The last line is the problem. It ends up overwriting my data above A40, as if there's nothing in A39.

I've also tried the following :
Sheet2.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).PasteSpecial paste :=xlPasteValues

The above works when I'm inserting the time stamps in the columns for Sheet1 through a Command button click with this :

Sub Button1_Click() Sheet1.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).Value = Time
End Sub

It doesn't seem to like that I'm using Sheet2.Range instead of Activesheet.Range or just Range and gives me an user object defined error. I cannot use either of the former options since Sheet2 is going to be hidden from the person using the workbook.

Any help would be appreciated !

Thanks,

-Budd

2. ## Re: Combining columns by finding last used row in a column

the line "Sheet2.Range("A2", "A36").PasteSpecial Paste:=xlPasteValues" is overwriting your data above A20 before you excecute the last line. Maybe try running your code, without the last line, and see if the problem is still there. In other words I'm not sure your problem really is with the last line.

3. ## Re: Combining columns by finding last used row in a column

Originally Posted by nimrod
the line "Sheet2.Range("A2", "A36").PasteSpecial Paste:=xlPasteValues" is overwriting your data above A20 before you excecute the last line. Maybe try running your code, without the last line, and see if the problem is still there. In other words I'm not sure your problem really is with the last line.
That's actually normal.

The last line is overwriting the data from the first paste. So what this looks like is a table of columns A to G which can have data in any row from 2 until 26. I'm trying to put all these columns together elsewhere on the sheet so I can order them and run calculations against them. The first table has to stay intact though for certain graphs I'm using on a third sheet. I could put all these together if the columns were right next to each other, but depending on what the user selects, you may only have 3-4 random columns with any actual data in them.

4. ## Re: Combining columns by finding last used row in a column

Hahaha...

Figured it out. I had this as my first line of the command button that called all this code :

Sheet2.Range("A41:A300").ClearContents

Don't script and drink, kids.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1