Loop to check for empty cell and retrieve row nb

[vadius]

Hi there,

I am extracting dates from one sheet to another.
As shown on the code below I created a king of loop that reads the content of a cell in column A, every 26 rows as the data I want is stored there.

Code :
=====================================================
Sub copydate()

Dim strValue As String
Dim strCellNum As String
Dim cleandate As String
Dim x As String
x = 2

For i = 2 To 6000 Step 26
strCellNum = "A" & i
strValue = Worksheets("Extract").Range(strCellNum).Value
Debug.Print strValue
Worksheets("Cleanprice").Range("A" & x).Value = Format(CDate(Right(strValue, 10)), "dd/mmm/yy")
x = x + 1
Next

End Sub

=====================================================

In the sheet where I have the row data, the last cell I want to retrieve the data is the 5436th row. The problem is that row data are regularly updated, so this number will change. If, in my code, I don't put exactly 5436 as the upper limit (for i=2 to X...) I have an error message.


So I would like to create a macro that reads the cells of column A (every 26 rows) and checks whether or not the cell is blank. If yes, I want the macro to give me the row number of the "last 26th cell" where I can find data. So that I can replace the upper limit in the code and don't have any error message.
I think it might be possible to do this with a kind of while ( while the 26th cell of all my loops is not empty, then extract the value. Else stop and the row number) but I don't how to put that in the orght way.

I hope it's clear.

Thanks

 

[JimM]

Does this work


Sub copydate()

Dim strValue As String
Dim strCellNum As String
Dim cleandate As String
Dim x As String
x = 2

For i = 2 To 6000 Step 26
if cells(i,1) <> "" then
strCellNum = "A" & i
strValue = Worksheets("Extract").Range(strCellNum).Value
Debug.Print strValue
Worksheets("Cleanprice").Range("A" & x).Value = Format(CDate(Right(strValue, 10)), "dd/mmm/yy")
x = x + 1
end if

Next

End Sub

 

[JimM]

Actually may be better to do

Sub copydate()

Dim strValue As String
Dim strCellNum As String
Dim cleandate As String
Dim x As String
x = 2

For i = 2 To 6000 Step 26
if cells(i,1)="" then exit sub
strCellNum = "A" & i
strValue = Worksheets("Extract").Range(strCellNum).Value
Debug.Print strValue
Worksheets("Cleanprice").Range("A" & x).Value = Format(CDate(Right(strValue, 10)), "dd/mmm/yy")
x = x + 1
Next

End Sub

 

[taurean]

You are looking last data filled row. Aren't you?

Dim lLastRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row

 

[vadius]

Hi

thanks. I am actually looking for the row number of the row where I can find data. Basically I created a loop to get the data of every 26 rows. So it checks Cell(A,2), then Cell(A,28) etc,.. until cell(A,5436) which is the last cell where I have data within the loop. Of course I have data row 5437, 5438,, but not anymore row number 5436+26, which is empty.

I want the macro to retrive the number 5436.

I sthat more clear ?

Cheers

 

[taurean]

I am still not sure but may be this:

Dim lLastRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
If ((lLastRow - 2) Mod 26) = 0 Then
lLastRow = lLastRow
Else
lLastRow = lLastRow - ((lLastRow - 2) Mod 26)
End If

 

[vadius]

Yes it is !

Thanks