5

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

K20GH
  • 6,032
  • 20
  • 78
  • 118

2 Answers2

6

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.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks. How would I go about copying that column though? I assume you use LastCol as the reference? – K20GH Aug 09 '12 at 12:33
  • 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. – Siddharth Rout Aug 09 '12 at 12:34
  • Brilliant - how do i manipulate that code you wrote to basically store the last column + 1 (ie, the next blank column) – K20GH Aug 09 '12 at 12:38
  • where are you copying from and where are you exactly pasting? Same sheets? different sheets? – Siddharth Rout Aug 09 '12 at 12:39
  • @K20GH Using Siddharth's sub, add this line of code: `ws.Cells(1, lastcol + 1).EntireColumn.Value = ws.Cells(1, lastcol).EntireColumn.Value` – Zairja Aug 09 '12 at 13:31
  • @SiddharthRout I'm copying it on the same sheet – K20GH Aug 10 '12 at 09:08
  • @Zairja I am trying to save the Last Column +1 as a variable and copy to there but get an error: LastCol1 = ws.Cells(1, LastCol + 1).EntireColumn.Value = ws.Cells(1, LastCol).EntireColumn.Value Sheets("Dashboard").Columns(LastCol).Copy Sheets("Reporting").Columns(LastCol1) Any suggestions? – K20GH Aug 10 '12 at 09:55
  • @K20GH: So if last column is E then you are trying to copy the value from say E1 to F1? – Siddharth Rout Aug 10 '12 at 09:58
  • @SiddharthRout If last column is E then trying to copy the whole of column E to the next blank column, which would always be the next along, ie. F So then also, F to G when it runs the next time – K20GH Aug 10 '12 at 10:09
  • 1
    Did you try `ws.Columns(LastCol).Copy ws.Columns(LastCol + 1)` – Siddharth Rout Aug 10 '12 at 10:10
  • @K20GH I'm glad you got it working. It's odd that the code in my comment didn't do the job, though. For anyone else who stumbles on this, the difference between assigning one range's values to another range (`ws.Columns(lastcol + 1).Value = ws.Columns(lastcol).Value`) versus copying is that the value-method does not preserve formatting and is slightly faster. Both are handy depending on your purpose. :) – Zairja Aug 10 '12 at 12:34
  • Just make sure the filters are always clear, or this may not always give an accurate result. `If ws.FilterMode Then ws.ShowAllData`. – Andy Dec 19 '20 at 08:28
0

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
Joel Stern
  • 1