0

I'm trying to look for the last row of data between column A and I and then duplicate the value to the row below which is empty.

Every time I run it, Excel crashes

Sub insert_row()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LastRow = LastRow

Dim lastrow_start As String
Dim lastrow_end As String
lastrow_start = "A" & LastRow
lastrow_end = "I" & LastRow

Dim lastrowregion As String
lastrowregion = lastrow_start & ":" & lastrow_end

Dim lastrowrange As Range
Set lastrowrange = Range(lastrowregion)

Dim rng As Range
Set rng = Range(lastrow_start)

Do While (rng.Value <> "")
    rng.Offset(1).insert
    lastrowrange.Copy rng.Offset(1)
    Set lastrowrange = rng.Offset(2)
Loop

End Sub

Is it just copying too much and causing a crash? It's only nine columns and they're all text apart from one cell which is a shape (button).

Community
  • 1
  • 1
Sam
  • 109
  • 9
  • 1
    **1** lastrowregion is a string. Use `Set rng = Range(lastrowregion)` **2** Use `.Find` to find the last row as shown [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Jul 25 '19 at 15:30
  • For the error, `lastrowregion` is a String which is what the message is trying to convey. – Brian M Stafford Jul 25 '19 at 15:30
  • You should define all your variables too since you're encountering type errors. – pgSystemTester Jul 25 '19 at 15:32
  • I've updated the original post with a new error - thanks for the help around setting it as a range correctly – Sam Jul 25 '19 at 15:43

1 Answers1

2

You are trying to set a String to a range object. To get the range use:

Set rng = Range(lastrowregion)

The Range you are getting is A2:I2. So your Do While will error because rng.Value is actually returning an Array. You could either loop through either the Range or the Array at that point if you intended on it being multiple cells.

If the goal is simply to copy the last row of data down one row then this method can be much simpler. You can simply set the Offset to equal the value of the last row. Since they are the same size it will just work.

To show this I used CurrentRegion but you could also do it with your A2:I2 Range.

Public Sub copyLastRowDown()
    Dim region As Range
    Set region = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    With region.Rows(region.Rows.Count)
        .Offset(1).Value = .Value
    End With
End Sub

Additional Notes

  • Use Option Explicit to ensure all variables are explicitly declared.
  • Declare and assign variables next to where they are going to be used, but place them in a reasonable place.
  • Do not use underscore case as this has special meaning with events and interfaces.
Robert Todar
  • 2,085
  • 2
  • 11
  • 31
  • That makes sense - the issue I'm having now is Excel crashes when I execute the code. I think the code is correct now but I can't actually see the results as it crashes. (I've updated original post with code) – Sam Jul 25 '19 at 15:52
  • Maybe there's an alternative to Copy and Pasting? I don't mind the values of the inserted row not being exactly the same, it can insert placeholder text for example, but I figured copy/paste would be easier as there is a shape in one cell that is assigned to a macro. – Sam Jul 25 '19 at 16:01
  • You have an infinite loop. Why do you even need a `Do Loop`? Are you just trying to get the last row to copy down one row? – Robert Todar Jul 25 '19 at 16:09
  • Yeah, that or what i described in my previous comment – Sam Jul 25 '19 at 16:24
  • I added an example to my answer. When working with the same sized Range you can simply set one range equal to the other. This is almost always better than copy and paste. – Robert Todar Jul 25 '19 at 16:27
  • Got it to work - your code is much more efficient than copy/paste for sure, thanks! The only problem with it is that it doesn't duplicate the shape (button macro) that is within the row like a copy/paste does - any ideas for a work around? – Sam Jul 25 '19 at 19:24
  • Glad you got it to work! That sounds like a separate question to this one. You can ask another question and reference this one in the post. – Robert Todar Jul 25 '19 at 19:37
  • Resolved it but having the code insert the shape each time, rather than duplicate it from the previous row - seems all good now! Thanks once again for your help – Sam Jul 25 '19 at 20:41