finding the first available cell in the column to paste a value into

[Pat_The_Bat]

Trying to create a range variable that will be the next available cell in the column (after the last used cell in that column, or just below the last used cell in that column)
When I step through the code, I get an error on the line of code that begins "Set LSTROW= ....."

Can anyone help me get un-stuck on this?



Sub LSTROW()


Dim LSTROW As Range


Set LSTROW = Worksheets("Doc Checklist").Range("C2").End(xlDown).Row + 1










With Sheets("Doc Request").Range("B17:B100").SpecialCells(xlConstants)
.Offset(, -1).Copy LSTROW
'The line above this needs to reference range = the next available cell in the column C
End With
On Error Resume Next
Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0








End Sub

 

[nardagus]

I'm not sure what are you trying to achieve but you can do this:

Dim LSTROW as Integer

with worksheets("Doc Checklist")
    LSTROW = .Range("C" & .Rows.Count).End(xlUP).Row + 1

    'fill next available cell with a new data
    .Range("C" & LSTROW) = "This was a next available cell"

end with

P.s. When you define a variable as Range you cannot then assign a number to it. It needs to be range.

 

[JoeMo]

The way that you have written the line that triggers an error, it appears you want the number of the last row +1. That's not a range object so the Set prefix is bad syntax.

If you wanted the range object it would be:

Set LSTROW = Worksheets("Doc Checklist").Range("C2").End(xlDown)

Note, however, that Range("C2").End(xlDown) will be the next filled cell after C2, and there may be one or more filled cells after that one so the +1 might be a filled cell.
Here's a procedure that will identify the row number of the next empty cell in col C after C2. This might be the same as LSTROW found by post #2 , but only if there are no empty cells between c2 and LSTROW - 1.

Sub FindNextAvaliableCellInColumn()
'Next empty cell after C2
Dim NxRw As Long, Blanks As Range
If IsEmpty(Range("C3")) Then
    NxRw = 3
Else
    On Error Resume Next
    Set Blanks = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    If Not Blanks Is Nothing Then
        NxRw = Blanks(1).Row
    Else
        NxRw = Cells(Rows.Count, "C").End(xlUp).Row + 1
    End If
    On Error GoTo 0
End If
MsgBox NxRw
End Sub

 

[Pat_The_Bat]

I'm not sure what are you trying to achieve but you can do this:

Dim LSTROW as Integer

with worksheets("Doc Checklist")
    LSTROW = .Range("C" & .Rows.Count).End(xlUP).Row + 1

    'fill next available cell with a new data
    .Range("C" & LSTROW) = "This was a next available cell"

end with

P.s. When you define a variable as Range you cannot then assign a number to it. It needs to be range.

Thanks for responding. I appreciate the help.
What I'm trying to do is copy items from a "pick list" on another sheet. Once the user puts an "x" next to an item on the picklist, and then hits the command button. the idea is to have those items with "x"'s next to them get added to the list.

I've got some code that works for when and where to copy the data from, but the part I'm struggling with is how to code for where to paste that data. I guess I was thinking I could create a variable that represents the next available cell in a column, and target any copy and pasting to that variable (since that variable will always equal the cell just below the last cell that has data in it.

 

[Pat_The_Bat]

Thanks for responding.
Wouldn't range("C2").end(xldown) find the last cell in the column that has data? The last used cell going down the column?

Also, see my response to Nardagus below. Any suggestions on what I'm doing here? I'm still not clear on solving this. I thought I would just define a variable to be the location of the cell just below the last cell in the column, and then +1, and then just use that variable as my location to paste the data that I've copied.
Any help is greatly appreciated. This problem is starting to drive me crazy!!!

hanks for responding. I appreciate the help.
What I'm trying to do is copy items from a "pick list" on another sheet. Once the user puts an "x" next to an item on the picklist, and then hits the command button. the idea is to have those items with "x"'s next to them get added to the list.

I've got some code that works for when and where to copy the data from, but the part I'm struggling with is how to code for where to paste that data. I guess I was thinking I could create a variable that represents the next available cell in a column, and target any copy and pasting to that variable (since that variable will always equal the cell just below the last cell that has data in it.

 

[nardagus]

Well as JoeMo wrote. Range("C2").end(xldown) will find the next nonempty cell. So if you have:

Cell1
Cell2
[empty cell]
Cell3
Cell4

Above command will give you Cell3, while you need to have row number (5 in this case) with Cell4 as you want to append new data starting from row 6

However if it happens there are no empty cells between first and last cell then yes. It will give you LSTROW properly.

You could give us your code so it would be easier to implement our solution.
In the meantime check this:

Dim LSTROW As Integer


With Worksheets("Doc Checklist")
    LSTROW = .Range("C" & .Rows.Count).End(xlUp).Row + 1


    'fill next available cell with a new data
    .Range("C" & LSTROW).PasteSpecial xlPasteAll
    
End With

You can paste it after your macro copies the source data.

 

[Pat_The_Bat]

Well as JoeMo wrote. Range("C2").end(xldown) will find the next nonempty cell. So if you have:

Cell1
Cell2
[empty cell]
Cell3
Cell4

Above command will give you Cell3, while you need to have row number (5 in this case) with Cell4 as you want to append new data starting from row 6

However if it happens there are no empty cells between first and last cell then yes. It will give you LSTROW properly.

You could give us your code so it would be easier to implement our solution.
In the meantime check this:

Dim LSTROW As Integer


With Worksheets("Doc Checklist")
    LSTROW = .Range("C" & .Rows.Count).End(xlUp).Row + 1


    'fill next available cell with a new data
    .Range("C" & LSTROW).PasteSpecial xlPasteAll
    
End With

You can paste it after your macro copies the source data.

This code set was working, and now all of a sudden it is throwing the error [h=2]'1004' PasteSpecial method of Range class failed.[/h]






Sub LR()
Dim LSTROW As Integer




With Worksheets("Doc Checklist")
LSTROW = .Range("C" & .Rows.Count).End(xlUp).Row + 1


With Sheets("Doc Request").Range("B2:B100").SpecialCells(xlConstants)
.Offset(, -1).Copy


End With




On Error Resume Next
Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0


'fill next available cell with a new data
.Range("C" & LSTROW).PasteSpecial xlPasteAll

End With


End Sub

 

[nardagus]

After you run COPY you cannot do other things on Ranges. You can navigate but DELETING, FORMATTING and other stuff like that causes excel to reset COPY command. If you want to remove rows with empty cells in column C in "Doc Checklist" sheet you need to do that before you COPY anything. So macro should do the following:

Delete rows with empty cells in "Doc Checklist" -> Copy data from "Doc request" -> Find next available row - > Paste copied data.
This code works properly:

Sub LR()

Dim LSTROW As Integer

On Error Resume Next
    Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

With Sheets("Doc Request").Range("B2:B100").SpecialCells(xlConstants)
    .Offset(, -1).Copy
End With

With Worksheets("Doc Checklist")
    LSTROW = .Range("C" & .rows.Count).End(xlUp).row + 1
    
    'fill next available cell with a new data
    .Range("C" & LSTROW).PasteSpecial xlPasteAll
End With

End Sub

 

[Pat_The_Bat]

After you run COPY you cannot do other things on Ranges. You can navigate but DELETING, FORMATTING and other stuff like that causes excel to reset COPY command. If you want to remove rows with empty cells in column C in "Doc Checklist" sheet you need to do that before you COPY anything. So macro should do the following:

Delete rows with empty cells in "Doc Checklist" -> Copy data from "Doc request" -> Find next available row - > Paste copied data.
This code works properly:

Sub LR()

Dim LSTROW As Integer

On Error Resume Next
    Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

With Sheets("Doc Request").Range("B2:B100").SpecialCells(xlConstants)
    .Offset(, -1).Copy
End With

With Worksheets("Doc Checklist")
    LSTROW = .Range("C" & .rows.Count).End(xlUp).row + 1
    
    'fill next available cell with a new data
    .Range("C" & LSTROW).PasteSpecial xlPasteAll
End With

End Sub

This is great. Really appreciate the support on this.
The only thing I need to figure out now is
1- how to make it so that once those items are copied and pasted, they don't get added to the list a second time
2- how to give the user the ability to reset/clear the list (that one should be easy)

I'll start new threads since those are different topics and this one is resolved.

Again, really appreciate the direction and support!

 

[nardagus]

You're welcome.

Since you are going to start a new thread I won't write a solution here. However both points are quite easy to do.