How to move to next blank cell?

Question

I have data on multiple sheets in a workbook that I want copied all to one sheet in that same workbook. When I run the macro, I would like it to start by deleting the current data in the "iPage Data Export" sheet and then replacing it with data from the other sheets.

I want the process to occur one column at a time since I may not bring over everything. Right now I am trying to learn how to do just one column.

I was able to get it to copy all of the contents of a column from one sheet, but when it moves to the next sheet, it overwrites the existing data. In the end, I only get one sheets worth of data copied.

Here are my 4 problems:

How do I make it clear the data on this sheet before running the routine?
How can I make it start each copy function at the bottom of that row (i.e. after the last cell with a value)? I have tried many of the suggestions on this and other boards without success. I will admit I am not very experienced in this.
How can I make it copy to a particular column (currently it just seems to default to A.
How can I concatenate multiple columns during the paste function? I.e. what if I want it to insert: A2&", "B2 instead of just A2

Sub CombineData()

    Dim Sht As Worksheet

    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "iPage Data Export" Then
            Sht.Select
            Range("C:C").Copy
            Sheets("iPage Data Export").Select
            ActiveSheet.Paste
        Else
        End If
    Next Sht

End Sub

It is preferred to have only a single question per post. @SO was generous to address them all where others might not be. Particularly generous since those questions are duplicated elsewhere on this site. [Last row](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) and [joining columns](http://stackoverflow.com/questions/8482759/simple-vba-array-join-not-working/8482852#8482852) and [clearing](http://stackoverflow.com/questions/19593907/clear-contents-of-cells-in-vba-using-column-reference/19594089#19594089) — Byron Wall, Jun 19 '15 at 22:01

SierraOscar · Answer 1 · 2015-10-08T20:07:46.520

How do I make it clear the data on this sheet before running the routine?

Sht.Cells.ClearContents

How can I make it start each copy function at the bottom of that row (i.e. after the last cell with a value)? I have tried many of the suggestions on this and other boards without success. I will admit I am not very experienced in this.

Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

In detail:

Rows.Count will return the number of rows in the sheet, so in the legacy style *.xls workbooks this would return the number 65,536. Therefore "C" & Rows.Count is the same as C65536
Range("C" & Rows.Count).End(xlUp) is the same as going to C65536 and pressing Ctrl + ↑ - The command End(xlDirection) tells the program to go the last cell in that range. In this case, we would end up at the last cell containing data in column C.
.Offset(1, 0) means that we want to return the range offset by an amount of rows and/or columns. VBA uses RC (Rows Columns) references, so whenever you see something like the Offset() function with two numbers being passed as the arguments, it usually relates to the row, and the column, in that order. In this case, we want the cell that is one row below the last cell we referenced.
All-in-all the phrase Range("C" & Rows.Count).End(xlUp).Offset(1, 0) means go to the last cell in column C, go up until we hit the last cell with data, and then return the cell below that - which will be the next empty cell.

How can I make it copy to a particular column (currently it just seems to default to A.

Range("C:C").Copy Destination:=Sheets("iPage Data Export").Range("A:A")

You can pass the Destination argument in the same line and actually bypass the clipboard (faster and cleaner)

How can I concatenate multiple columns during the paste function? I.e. what if I want it to insert: A2&", "B2 instead of just A2

Lets say you wanted to reference column A, B, and F - just use:

Range("A1, B1, F1").EntireColumn

To summarise, you could streamline your existing code to something like (untested):

Sub CombineData()

Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "iPage Data Export" Then
            Sht.Range("C1:C" & Cells(Sht.Rows.Count, 3).End(xlUp).Row).Copy Destination:=Sheets("iPage Data Export").Range("A:A")
        End If
    Next

End Sub

Solid answer with good explanations. I think the concatenation bit refers to putting all the values in a single cell since OP called out the normal `&` operator but they might clarify that different. — Byron Wall, Jun 19 '15 at 21:50
@Byron Yeah that last one was a little unclear so just did what I could with it. — SierraOscar, Jun 19 '15 at 21:51
for the concat operation, the easiest for lots is to bring each row into a vector array by transposing the row twice then using Join() like `Range(A1) = Join(Application.Transpose(Application.Tranpose(Range(B1:G1)))`. Alternatively, a loop works. Or, most simply for a couple of columns, just specify with & — Cor_Blimey, Jun 19 '15 at 22:07
@SO could it not be `Destination:=Sheets("iPage Data Export").Range("A1")` — Davesexcel, Jun 20 '15 at 13:54
@Davesexcel sure, it can be any address as long as it's a valid range object. — SierraOscar, Jun 20 '15 at 13:55

score 2 · Answer 2 · answered Jun 19 '15 at 21:36

This should do for the copying:

Sub CombineData()
    Dim sheet As Worksheet

    For Each sheet In Worksheets
        If (sheet.Name <> "iPage Data Export") Then
            sheet.Select
            Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
            Selection.Copy
            Worksheets("iPage Data Export").Activate
            Cells(1, ActiveCell.SpecialCells(xlCellTypeLastCell).Column + 1).Select
            ActiveSheet.Paste
        End If
    Next
End Sub

For the concatenation you need to be more specific - but I guess you should open a new question with a clearer focus if you need specific help on that.

How to move to next blank cell?

2 Answers2

Linked