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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
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.


Code:
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
If Range("G1").Value <= 151 then
   For x = Range("G1").Value to 151 step 50
'rest of code
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
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. ..

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

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can you tell us in plain English, in an understandable way, what you are doing / trying to do within the double loop?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
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


Code:
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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).

Code:
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

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
Thanks Wigi, :) the code is working with "Next r" in the end instead of Next x
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top