# I don't want the empty rows in the array, I know why but...

#### most

##### Board Regular
This script creates a lot of empty rows in the arrays. I understand why, but I don't know how to write the code so it won't, some pointers please.

Code:
``````Sub GroundogDay() Dim i As Integer
Dim Count As Integer
Dim ArrDATE(1 To 33) As Variant
Dim ArrROW(1 To 33) As Variant

For i = 3 To 33
If Sheets("Feb").Range("A" & i).Value = "Monday" Then
ArrDATE(i) = Sheets("Feb").Range("B" & i).Value
ArrROW(i) = Sheets("Feb").Range("A" & i).Row
Else
End If
Next i

For i = LBound(ArrDATE) To UBound(ArrDATE)
Debug.Print ArrDATE(i)
Next i
For i = LBound(ArrROW) To UBound(ArrROW)
Debug.Print ArrROW(i)
Next i
End Sub``````

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
If the issue is that the data is blank, then just add an IF statement to your code to check the value before adding it to your array.
You already have an IF statement check to see if Column A is "Monday". Add another IF under that to check to see if column B is not blank.

No, that won't compute. First If statement will already take care of that. I believe the problem is that I have dimension the array to 31 lines from the beginning, (Sorry I didn't point that out from the beginning).

Some clarifications, column A contains all weekdays for a month, the script loops through and should point out row number and the date from column B for all Mondays. Which is does, but it also includes the empty lines.
Maybe there is a better way to do this!?

Last edited:
Another option
Code:
``````   Dim i As Long, j As Long
Dim ArrDATE(1 To 33) As Variant
Dim ArrROW(1 To 33) As Variant

For i = 3 To 33
If Sheets("Feb").Range("A" & i).Value = "Monday" Then
j = j + 1
ArrDATE(j) = Sheets("Feb").Range("B" & i).Value
ArrROW(j) = Sheets("Feb").Range("A" & i).Row
End If
Next i
ReDim Preserve ArrDATE(1 To j)
ReDim Preserve ArrROW(1 To j)

For i = LBound(ArrDATE) To UBound(ArrDATE)
Debug.Print ArrDATE(i)
Next i
For i = LBound(ArrROW) To UBound(ArrROW)
Debug.Print ArrROW(i)
Next i
End Sub``````

@Fluff, Excel complaint over "ReDim Preserve ArrDATE(1 To j)", Array already dimensioned.
But with your changes and with some input from Joe4 link I got it to work, THANKS A LOT!

Code:
``````[/COLOR]Sub GroundhogDay()   Dim i As Long, j As Long
Dim Count As Integer
Dim Cell As Range

For Each Cell In Range("A3:A33")
If (Cell.Value = "Monday") Then Count = Count + 1
Next Cell

ReDim ArrDATE(1 To Count)
ReDim ArrROW(1 To Count)

For i = 3 To 33
If Sheets("Feb").Range("A" & i).Value = "Monday" Then
j = j + 1
ArrDATE(j) = Sheets("Feb").Range("B" & i).Value
ArrROW(j) = Sheets("Feb").Range("A" & i).Row
End If
Next i

Debug.Print "J: "; j

For i = LBound(ArrDATE) To UBound(ArrDATE)
Debug.Print ArrDATE(i)
Next i
For i = LBound(ArrROW) To UBound(ArrROW)
Debug.Print ArrROW(i)
Next i
End Sub[COLOR=#333333]``````

Try this.
Code:
``````Dim i As Long, cnt As Long
Dim ArrDATE() As Variant
Dim ArrROW() As Variant

ReDim ArrDATE(1 To 33)
ReDim ArrROW(1 To 33)

For i = 3 To 33
If Sheets("Feb").Range("A" & i).Value = "Monday" Then
cnt = cnt + 1
ArrDATE(cnt) = Sheets("Feb").Range("B" & i).Value
ArrROW(cnt) = Sheets("Feb").Range("A" & i).Row
End If
Next i
ReDim Preserve ArrDATE(1 To cnt)
ReDim Preserve ArrROW(1 To cnt)

For i = LBound(ArrDATE) To UBound(ArrDATE)
Debug.Print ArrDATE(i)
Next i

For i = LBound(ArrROW) To UBound(ArrROW)
Debug.Print ArrROW(i)
Next i``````

You can replace the initial loop with
Code:
``````   Dim Cnt  As Long
Cnt = Application.CountIf(Range("A3:A33"), "Monday")
ReDim ArrDATE(1 To Cnt)``````
I would also advise against using VBA keywords (such as Count) as variable names, because it can cause unforeseen problems.

Replies
12
Views
164
Replies
5
Views
58
Replies
2
Views
101
Replies
0
Views
60
Replies
8
Views
244

1,203,082
Messages
6,053,420
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back