Finding next non-empty row

Question

I have a quesiton, I want to make a some easy code that check value and then will find next No empty cell. I try to do like this, but I have problem:

Sub test()
Dim ark1 As Worksheet, ark2 As Worksheet
Dim i As Long, j As Long, lastRow As Long, nextRow As Long, actCell As Long, actCell2 As Long
Set ark1 = Worksheets("Arkusz1")
Set ark1 = Worksheets("Arkusz2")

For i = 1 To 20
    If ark1.Cells(i, "A") = ark1.Cells(1, "B") Then
        ark1.Cells(i, 1).Select
        actCell = ActiveCell.Row
        MsgBox actCell
    End If
Next i

For i = actCell To 20
    ark1.Cells(i, "A").Select
        If IsEmpty(ActiveCell) Then
            Next i
            Else
            ark1.Cells(i, "A") = 1
        End If
 Next i
End Sub

"I have problem" is not very specific. Are you getting an error? If so, what error and on what line? — braX, Nov 18 '19 at 09:59
You have set the same variable (`ark1`) twice, each for a different sheet. You probably meant to use `Set ark2 = Worksheets("Arkusz2")`. Start with that. Furthermore, avoiding `.Select` and stuff like `ActiveCell` is recommended. You can find some inspiration on how to do so [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Even so, with a better explaination of what you are trying to achieve, I got a feeling this can all be simplified =) — JvdV, Nov 18 '19 at 10:03
I want to coppy all cells betwen this two "full" cells to other sheet. I know that maybe it could be done in other way but I do not have big skills in VBA so I try to do it as I can. — gajus21, Nov 18 '19 at 10:31
If you are willing to add some sample data, expected results and current result along with your code, I'm sure we can help you out with that. Have a look at [ask] a question with a [mcve]. — JvdV, Nov 18 '19 at 10:36

score 0 · Answer 1 · answered Nov 18 '19 at 10:41

Thanks to JvdV I found solution, maybe it will be useful to someone:

Sub test()
Dim ark1 As Worksheet
Dim i As Long, j As Long, lastRow As Long, nextRow As Long, actCell As Long, actCell2 As Long
Set ark1 = Worksheets("Arkusz1")

For i = 1 To 20
    If ark1.Cells(i, "A") = ark1.Cells(1, "B") Then
        ark1.Cells(i, 1).Select
        actCell = ActiveCell.Row
        MsgBox actCell
    End If
Next i

' Next no empty cell
For i = actCell + 1 To 20
        If IsEmpty(ark1.Cells(i, "A")) Then
            Else
            ark1.Cells(i, 1).Select
            actCell2 = ActiveCell.Row
            MsgBox "The active cell is not empty   " & actCell2
            Exit For
        End If
 Next i
End Sub

Finding next non-empty row

1 Answers1