removing steps in code except use cell reference .....

[omairhe]

Peace all,

how to change range set by the loop with mathematics (Forx =1 To 151 Step 50) in the code below, instead look for range with a cell value in cell G1 upto G20.

for instance cell value in G1 = 51 .. Now goto row 51 and apply the rest of the code to perform from there. and loop it for the cell reference in G2.G3.G4.... so on.

thanks.

Sub Delete()
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim lr As Long
    Dim cellrow As Range
    
    For x = 1 To 151 Step 50
        
            Cells(x, 1).Select
            Set cellrow = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 255))
            z = Application.WorksheetFunction.CountA(cellrow)
            lr = Range(Cells(x, 1), Cells(x, 1).End(xlDown)).Count
            
            If z = 0 Then
            For y = 1 To lr
            Set cellrow = cellrow.Resize(y)
            cellrow.Select ' Checking resize
            z = Application.WorksheetFunction.CountA(cellrow)
            If z > 0 Then
            Set cellrow = cellrow.Resize(y - 1)
            cellrow.Delete shift:=xlShiftUp
            y = lr
            End If
            Next y
            End If
    Next x
End Sub

 

[JoeMo]

If Range("G1").Value <= 151 then
   For x = Range("G1").Value to 151 step 50
'rest of code

 

[omairhe]

To make it clearer consider cell G1 with a value 4 and G2 with value 92.. these 2 values has no link with one another but completely random. So mathematical step is not required. instead loop each cell value from G1 to G2 and onwards. ..

If Range("G1").Value <= 151 then
   For x = Range("G1").Value to 151 step 50
'rest of code

 

[wigi]

Can you tell us in plain English, in an understandable way, what you are doing / trying to do within the double loop?

 

[JoeMo]

For each c in Range("G1","G20")
   If c.Value <= 151 then       
       For x =c.Value to 151 step 50   
       'rest of code
       Next x
  End if
Next c

 

[omairhe]

Cell G1 to G20 has value = 1 - 1001

These values are row numbers..
for instance value 1 is the row 1 on excel sheet. value 10 in any of the G1-G20 is also a row number in excel sheet.

the rest needs to perform with reference to the row number instead by calculating the 50 step formula.

I need a formula free code.

a similar code that does almost the same is...

Sub insert()
Dim start As Long
Dim rows As Long
Dim j As Long
On Error Resume Next
For j = 1 To 21
Call InsertFormulas
start = Range("E" & j)
rows = Range("F" & j)
Cells(start, 1).Resize(rows).EntireRow.insert
Next j
End Sub

For each c in Range("G1","G20")
   If c.Value <= 151 then       
       For x =c.Value to 151 step 50   
       'rest of code
       Next x
  End if
Next c

 

[wigi]

Below is the literal answer to yoiur question, but I cannot say that I follow what you are trying to do within the double loop.
(Even when I asked, you did not go into the details).

Sub DeletingRows()    Dim r As Long
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Dim lr As Long
    Dim cellrow As Range


    For r = 1 To 20
        
        x = Range("G" & r).Value
        Cells(x, 1).Select
        Set cellrow = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 255))
        z = Application.WorksheetFunction.CountA(cellrow)
        lr = Range(Cells(x, 1), Cells(x, 1).End(xlDown)).Count


        If z = 0 Then
            For y = 1 To lr
                Set cellrow = cellrow.Resize(y)
                cellrow.Select    ' Checking resize
                z = Application.WorksheetFunction.CountA(cellrow)
                If z > 0 Then
                    Set cellrow = cellrow.Resize(y - 1)
                    cellrow.Delete shift:=xlShiftUp
                    y = lr
                End If
            Next y
        End If
    Next x
End Sub

 

[omairhe]

Thanks Wigi, the code is working with "Next r" in the end instead of Next x

 

[wigi]

Thanks Wigi, the code is working with "Next r" in the end instead of Next x

To avoid these kind of issues, just use "Next" instead of "Next r".