Forum Discussion

kdliske's avatar
kdliske
Copper Contributor
Mar 04, 2025
Solved

Need help tweaking VBA code so I can add more columns to spreadsheet

Hi, I have a macro with code that allows me to press a button that then moves rows with a certain status to another worksheet. I need to add two more columns to the worksheets, but when I do the macro stops working. Any suggestions? Code is below.

 

Sub Button3_Click()

'Updated by Kutools for Excel 2017/8/28

    Dim xRg As Range

    Dim xCell As Range

    Dim I As Long

    Dim J As Long

    Dim K As Long

    CommandButton3_Click = "Completed"

    I = Worksheets("Tasks").UsedRange.Rows.Count

    J = Worksheets("Completed").UsedRange.Rows.Count

    If J = 1 Then

       If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0

    End If

    Set xRg = Worksheets("Tasks").Range("I3:I" & I)

    On Error Resume Next

    Application.ScreenUpdating = False

    For K = 1 To xRg.Count

        If CStr(xRg(K).Value) = "Complete" Then

            xRg(K).EntireRow.Copy Destination:=Worksheets("Completed").Range("A" & J + 1)

            xRg(K).EntireRow.Delete

            If CStr(xRg(K).Value) = "Complete" Then

                K = K - 1

            End If

            J = J + 1

        End If

    Next

    Application.ScreenUpdating = True

End Sub

  • The code looks for the value "Completed" in column I.

    If you insert one or more columns to the left of column I, the status will end up in another column, so you'll have to adjust the code. For example, if status is now in column K, change the line

        Set xRg = Worksheets("Tasks").Range("I3:I" & I)

    to

        Set xRg = Worksheets("Tasks").Range("K3:K" & I)

    If that is not correct, please provide more detailed information.

  • kdliske's avatar
    kdliske
    Copper Contributor

    Hi, I keep replying but then don't see my reply after, so apologies if this suddenly appears 3x.

     

    This worked perfectly, but opened up a new problem. I am re-using an old workbook and tweaking to update for a new use. I have deleted all of the rows and cleared them, but when I click 'Completed' it is still moving them to the end of the previous table (ex. row 2557) rather than re-starting (ex. row 3). I have found bits of code and suggestions to re-set this, but none of them have worked. Any suggestions? Thank-you again.

    • This forum doesn't work well, I do see 3 replies now. What if you replace the lines

          J = Worksheets("Completed").UsedRange.Rows.Count

          If J = 1 Then
             If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
          End If

      with

          J = Worksheets("Completed").Range("K1000000").End(xlUp).Row

      where K is the column with the status.

         

  • kdliske's avatar
    kdliske
    Copper Contributor

    Thank-you.

    Now my issue seems to be that since I am re-using an old spreadsheet (but tweaking it for a new use), the range is stuck as that of the old spreadsheet (ex. it adds the 'Completed' row to row 2557 rather than re-starting at row 3). I have been trying bits of code I am finding online but no dice on re-setting this. Any suggestions?

  • ccaab's avatar
    ccaab
    Copper Contributor

    Try to use below code for your macro

     

    Sub Button3_Click()

        Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim J As Long
        Dim K As Long

        CommandButton3_Click = "Completed"

        I = Worksheets("Tasks").UsedRange.Rows.Count
        J = Worksheets("Completed").UsedRange.Rows.Count

        If J = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then J = 0
        End If

        Set xRg = Worksheets("Tasks").Range("I3:I" & I)

        On Error Resume Next
        Application.ScreenUpdating = False

        For K = 1 To xRg.Count
            If CStr(xRg(K).Value) = "Complete" Then
                ' Copy the entire row along with two additional columns to the destination sheet
                xRg(K).EntireRow.Resize(1, xRg(K).EntireRow.Columns.Count + 2).Copy Destination:=Worksheets("Completed").Range("A" & J + 1)
                xRg(K).EntireRow.Delete
                If CStr(xRg(K).Value) = "Complete" Then
                    K = K - 1
                End If
                J = J + 1
            End If
        Next

        Application.ScreenUpdating = True

    End Sub

  • The code looks for the value "Completed" in column I.

    If you insert one or more columns to the left of column I, the status will end up in another column, so you'll have to adjust the code. For example, if status is now in column K, change the line

        Set xRg = Worksheets("Tasks").Range("I3:I" & I)

    to

        Set xRg = Worksheets("Tasks").Range("K3:K" & I)

    If that is not correct, please provide more detailed information.

    • kdliske's avatar
      kdliske
      Copper Contributor

      Thank-you, that worked perfectly. Now I'm running into another issue. I am trying to re-use an old spreadsheet for a new purpose and the code is remembering the old table range (ex. so rather than re-starting by moving the 'Completed' row to the first empty line (row 3), it is moving it to row 2574). How would I re-set that? 

Resources