Help adjusting a variable- row/ column counter

[mnty89]

I'm trying to tweak a macro to add a new loop and I have it 90% figured out, but I need help on one simple aspect.

Basically it loops through rows and columns so the original way it was set up uses these two variables to decide how many rows and columns to loop through

intRows = ws.UsedRange.Rows.Count
intCols = ws.UsedRange.Columns.Count

The row counter is fine, but the column counter is flawed because if I have 50 values to loop through in row 1, but only 10 in row 2, then it will repeat the loop 50 times since technically the worksheet extends to 50 rows so it uses that as the range.

I need help adjusting this to reset the column based on the current row used count not the whole worksheet.. Anyone have an idea? Sure this is simple but I'm 100% self learning so I only have other examples to work from and cant find a 100% similar application that I'm able to recreate from scratch.

At the end of the day I want to redefine intcols to just loop through the needed amount of times. The macro itself is incredibly long and complex, but the general format is-


Set rows to loop through

start actions set for each row

loop through each column and repeat actions for all columns

when finished looping through columns go to next row and repeat.


Right now this is kicked off with setting the rows to loop through as For i = 2 To intRows, so that it begins on the first non header row,

then skips to next i.

The columns part within that loop begins with For j = 6 To intCols, to start where the used info starts in column 6. This then goes to next j over and over but will loop needlessly through blank columns to the count of the most columns in any single row..

Hopefully this makes sense.

I need a way to insert a new column counter per looping j..

 

[V_Malkoti]

From your problem description it sounds like the blank cells in row are contiguous. If so, then you can just test for blank and exit the j value loop, thereby skipping remaining columns.

For i = 2 To intRows
    ' your code
    For j = 1 To intCols
        If .Cells(i, j).Value = "" Then Exit For
        ' More code
    Next j
    ' Remaining code
Next i

 

[mnty89]

YES! You did it, thanks!