0

I have the below code which takes rows of data from one worksheet(DataSheet) that has "Actioned" in Column C and should paste it to the next available row on the worksheet (Actioned). The issue at hand is that it doesn't paste it on the next available row, its pasting to Row 2700.

I've cleared contents on all the cells and restarted the workbook but it still doesn't paste it onto the next free row.

can anyone see where I'm going wrong?

    Dim xRg As Range
    Dim xCell As Range
    Dim i As Long
    Dim J As Long
    Dim K As Long
    i = Worksheets("DataSheet").UsedRange.Rows.Count
    J = Worksheets("Actioned").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Actioned").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("DataSheet").Range("C2:C" & i)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Actioned" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Actioned").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "Actioned" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
   
    Application.ScreenUpdating = True 
End Sub ```

I am still a novice at VBA so please bare with my ignorance.
Tomas Joe Gemine
  • 81
  • 10
  • Did you previously have data up until row 2700? Sometimes (I don't know when really) Excel remembers the previous data range if it was larger and jumps there when Ctrl+End or Ctrl+DownArrow is pressed, even if it's nothing there anymore, and perhaps `.UsedRange` can do the same... – Dávid Laczkó Mar 22 '21 at 07:37
  • yeah there was previously, but i have cleared all the contents. are you aware of any sort of work around? – Tomas Joe Gemine Mar 22 '21 at 07:38
  • Delete the rows (not just the cell contents), and try again. – Dávid Laczkó Mar 22 '21 at 07:40
  • 2
    See https://stackoverflow.com/a/11169920/7599798 – FunThomas Mar 22 '21 at 08:28
  • I think your code could be a lot more efficient - but I'd need to see a screenshot of your `DataSheet` and `Actioned` sheets before I could suggest anything. –  Mar 22 '21 at 08:39

1 Answers1

0

UsedRange can take into account the formatting of empty cells which is not visible to you.

I suggest to use the following method to get the last row of a specific column:

i= Worksheets("DataSheet").Range("C" & Worksheets("DataSheet").Rows.Count).End(xlUp).Row

or:

Dim wsDS As Worksheet: Set wsDS = Worksheets("DataSheet")
Dim iRows As Long

iRows = wsDS.Range("C" & wsDS.Rows.Count).End(xlUp).Row
Bart Wille
  • 1
  • It might work with the original condition, but if the OP deletes the empty rows as I suggested in my comment, won't `.End(xlUp)` jump to the first row? – Dávid Laczkó Mar 22 '21 at 07:52