CODEWITHSUNDEEP
vbaexcel
Paul
Paul

Reputation: 1176

"Constant Expression required" error in VBA

I am new to VBA and I am trying to write a simple program that copies a sheet to another sheet and then pastes it with some of the rows selectively deleted. As part of my program I am trying to simply save the number of used rows as an integer, what I call shorterLen in the following code. However, when I try to construct an array of length shorterLen, I get a compile error: "constant expression required." I don't understand this because I the code I am using to find the number of used rows (Sheets(2).UsedRange.Rows.count) should be outputing a constant integer. Also I tried it before and it was working fine. Is there something I am missing? I have asked a friend who is an expert with VBA and he didn't know. Any help would be greatly appreciated.

Sub Macro1()
Dim i As Integer
Dim numRows As Integer
numRows = Sheets(2).UsedRange.Rows.count
Debug.Print (numRows)
shorterLen = numRows - 11

Dim securityInd(shorterLen) As Integer

For i = 1 To shorterLen

If Not IsEmpty(ActiveSheet.Cells(i, 4)) Then
securityInd(i) = 1
Else:
securityInd(i) = 0
End If
Next i
ActiveSheet.Copy
Sheets.Add.Name = "sheet1"
Sheets("sheet1").select
ActiveSheet.paste

For i = 1 To numRows - 11
If securityInd(i) = 0 Then
Sheets("sheet1").Rows(i).Delete
End If

Next i

End Sub

Upvotes: 1

Views: 6047

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

To change an array dynamically like that use ReDim instead of Dim

Dim is for declaration of type in general. Redim Is specifically for changing array size, and setting a dynamic arrays size for the first time counts as changing.

Upvotes: 3

Related Questions