Copy last column with data on specified row to the next blank column

Question

I have a spread sheet and I need to look for the last column that has data in it. Then I need to copy this column and copy it to the next blank column.

Is there a way to do this?

I've managed to do it with rows using:

lastrowSrc = Sheets("Overview").Range("B" & Rows.Count).End(xlUp).Row

However this puts B12 in the range, using columns.count simply puts in the number of the column, not the letter

• Siddharth Rout: Yes. What have you tried so far?
• K20GH: I've managed to do it with rows ie: lastrowSrc = Sheets("Overview").Range("B" & Rows.Count).End(xlUp).Row
• Siddharth Rout: I see. For columns it is slightly different.. posting a code
• K20GH: Thanks - I've just updated my question to make it a little clearer

Answer 1

To get the exact column in a worksheet, use this code.

Option Explicit
Sub Sample()
 Dim ws As Worksheet
 Dim LastCol As Long
 Set ws = Sheets("Sheet1")
 '~~> This check is required else .FIND will give you error on an empty sheet
 If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
 LastCol = 1
 Else
 LastCol = ws.Cells.Find(What:="*", _
 After:=ws.Range("A1"), _
 Lookat:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Column
 End If
 Debug.Print LastCol
End Sub

---

EDIT: This is courtesy @brettdj. You can also use the range object to find the last column

Option Explicit
Sub Sample()
 Dim ws As Worksheet
 Dim LastCol As Long
 Dim rng As Range
 Set ws = Sheets("Sheet1")
 Set rng = ws.Cells.Find(What:="*", _
 After:=ws.Range("A1"), _
 Lookat:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False)
 If rng Is Nothing Then
 LastCol = 1
 Else
 LastCol = rng.Column
 End If
 Debug.Print LastCol
End Sub

---

To get the last column of a particular row, say row 1 use this

 Debug.Print ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Where ws is your relevant worksheet.

Similarly for Row see this.

• K20GH: Thanks. How would I go about copying that column though? I assume you use LastCol as the reference?
• Siddharth Rout: Yes :) Sheets("Sheet1").Columns(LastCol).Copy But of you want to copy from the last column of a specific row then use this Sheets("Sheet1").cells(r,LastCol).Copy where r is the relevant row.
• K20GH: Brilliant - how do i manipulate that code you wrote to basically store the last column + 1 (ie, the next blank column)
• Siddharth Rout: where are you copying from and where are you exactly pasting? Same sheets? different sheets?
• Siddharth Rout: Did you try ws.Columns(LastCol).Copy ws.Columns(LastCol + 1)

Answer 2

I found that some of the answers didn't work for my worksheet that had a few rows at the end that were shorter than the others in the worksheet. The code provided just gives the last column of the last row of the worksheet. Instead, I used a loop around code to find the last column in a row, using the Find example to get the last row in the workbook.

Sub Sample()
 Dim ws As Worksheet
 Dim CurrRow, RowLastCol, LastRow, LastCol As Long
 Set ws = Sheets("Sheet1")
 '~~> This check is required else .FIND will give you error on an empty sheet
 If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
 LastCol = 1
 Else
 LastCol = 0
 LastRow = ws.Cells.Find(What:="*", _
 After:=ws.Range("A1"), _
 Lookat:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Row
 ' Loop through all the rows of the sheet saving off the highest column count
 For CurrRow = 1 to LastRow
 RowLastCol = ws.Cells(CurrRow, Columns.Count).End(xlToLeft).Column
 If RowLastCol > LastCol Then
 LastCol = RowLastCol
 End If
 Next CurrRow
 End If
 Debug.Print LastCol
End Sub