Count Rows With Offset

**pezalmendra** · 01-01-2013, 09:06 PM

Hi!

Does someone knows the code for counting rows with an offset?

Before I was using the code bellow:

lngLastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

I would like to use that same code but counting with an offset of 5 rows (every 5 rows I have the data to count)

Any help would be much appreciated.

Regards and happy 2013

**mike7952** · 01-01-2013, 09:54 PM

Not exactly sur what your wanting. Can you upload a sample workbook?

**pezalmendra** · 01-02-2013, 06:48 AM

Hi,

Attached an example workbook.

Thanks!!!

**mike7952** · 01-02-2013, 08:27 AM

This should get you going

Option Explicit
Sub Weather()
Dim wsHoja1 As Worksheet
Dim wsHoja2 As Worksheet
Dim i As Long, ii As Long, destRow As Long
Dim sDate As String

 Set wsHoja1 = Sheets("Weather")
 Set wsHoja2 = Sheets("Data")
 
 destRow = 9
For i = 11 To wsHoja1.Cells(Rows.Count, "b").End(xlUp).Row Step 5
    sDate = wsHoja1.Cells(i, "B").Value
    For ii = 1 To 4
        If Not wsHoja1.Cells(i, "B").Offset(ii) = vbNullString Then
            wsHoja2.Cells(destRow, "B").Value = sDate
            wsHoja2.Cells(destRow, "C").Value = wsHoja1.Cells(i, "B").Offset(ii)
            wsHoja2.Cells(destRow, "E").Value = wsHoja1.Cells(i, "D").Offset(ii)
            wsHoja2.Cells(destRow, "F").Value = wsHoja1.Cells(i, "E").Offset(ii)
            destRow = destRow + 1
        End If
    Next
Next
 Set wsHoja1 = Nothing
 Set wsHoja2 = Nothing
End Sub