Loop to select and delete a row

[JonElCanche]

Below is the loop. An error occurs at Rows("y:y").Select
The y value that would be found in cell C500 would always match the row that would be selected and deleted. For example if cell C500 had the value 13, I would want Rows("13:13").select and then deleted. Any ideas?



Sub Macro2()
'
' Macro2 Macro
'


'
For y = 1 To 100


If Range("c500").Value = y Then
Rows("y:y").Select
Selection.Delete Shift:=xlUp
Sheets("Dashboard").Select
End If


Next y


End Sub

 

[b.downey]

The issue is that "y:y" is a string rather than variable. The y's don't result in a number between 1 and 100 as desired by the loop, but rather just the constant "y:y"

 

[Michael M]

Try

Sub Macro2()
Dim y As Long
    For y = 1 To 100
        If Range("c5").Value = y Then Rows(y).Delete
    Next y
Sheets("Dashboard").Activate
End Sub

 

[JonElCanche]

That worked like a charm Michael, thank you. What is the difference between your code and mine? We both used the variable y to specify the row?

Try

Sub Macro2()
Dim y As Long
    For y = 1 To 100
        If Range("c5").Value = y Then Rows(y).Delete
    Next y
Sheets("Dashboard").Activate
End Sub

 

[JonElCanche]

I just tried to use this exact same code on a different page and stepping through it everything works perfectly until it deletes the desired row. After deleting the correct row it tries to continues through the loop as it should but errors. Any ideas why that would be? After the desired row is deleted Range("c500").Value does result in an #N/A because the corresponding item is no longer in the list, but that is the same as the other page where everything works flawlessly.

Try

Sub Macro2()
Dim y As Long
    For y = 1 To 100
        If Range("c5").Value = y Then Rows(y).Delete
    Next y
Sheets("Dashboard").Activate
End Sub

 

[Michael M]

In regard to your first question, see b.downeys post
For the 2nd question try reversing the loop

Sub Macro2()
Dim y As Long
    For y = 100 To 1 step -1
        If Range("c5").Value = y Then Rows(y).Delete
    Next y
Sheets("Dashboard").Activate
End Sub