Excel VBA Loop Through Rows in a Table or Range

[@dusty975]

This is simple and brilliant, thank you very much.

[@jobinthomas25]

Thank you sir!! You saved me some agony today..lol

[@randomnetizen3681]

Great vid! Solved my problem. :)

[@toughman2013]

3:43 the variable Row havent been Defined, how was it able to run with complie error??

[@SpacedudeGFX]

Thank you!

[@user-mk2lh2ig7f]

I have the current VBA
Sub Subtract()
cPart = Sheet3.Range("a2, a1000")
cQty = Sheet3.Range("b2, b1000")
'loop
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) = cPart Then
'found it
Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty
Exit Sub
End If
Next i
End Sub
This only runs for 1 row, but I would like for it to repeat through every active row.. Please help

[@123456789mumbai]

Hello everyone, need your help if anyone can share some input
I need to select 10 items in fieldname which are available in sheet 1 using vba. What code can be used?

[@rajsurendra7040]

what if I wanted to skip specific rows without any condition like if I want to read only rows 2,3,4 and 7,8,9 then 17 and 21. is it possible to do it?

[@syntaxbyte]

You could use Row.Row to get the row number and then go to next row if you want to skip that number.

[@pmodocs3081]

Can you use currentregion to get the rows?

[@syntaxbyte]

Yes, you should be able to use it in the same way.

[@ashleshas4205]

Hi thank you for the informative video. Is there any columns can be looped as well?

[@syntaxbyte]

You can loop columns by using the Columns property instead of the Rows property.

[@ashleshas4205]

@@syntaxbyte Thank you so much! I was trying to use a command button (add data) to copy a column content (that keeps changing) to another column (separate sections of the sheet). So everytime the button is clicked the same column data is copied to a new column on the right. I am have very basic knowledge of Excel. Could you please help me out? Or help me with a link that has a video on this?

[@elgs1980]

Where is the definition of FormatAsPhoneNumbers?

[@sarahsardella2927]

Very nice! I'm very new to Excel VBA. I'm trying to assign the first row for my loop to run if a targeted cell is not blank. Would you be able to help me with this using your code model?
This is snippet of my code. It will run fine if there is a valid condition in Row2, but will not continue onto Row3 if it is not valid or blank.
Dim r As Long
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim mydate As Date
Dim mydate2 As Date
mydate = Date
mydate2 = mydate + 6
r = 2
Do While Worksheets("PEF").Cells(r, 15) >= mydate And Worksheets("PEF").Cells(r, 15)

[@syntaxbyte]

Not sure your code aligns with what your trying to do. If the date is between Date and Date + 6, looks like you've got yourself an infinite loop to me. I don't see anything in this code that checks whether a cell is blank or not.

[@sarahsardella2927]

@@syntaxbyte correct, I do not have anything written checking for the blank cell as I'm unsure how to achieve.
And yes, the loop for the date check within 7 days of today does work, however I need an additional loop for the row assignment. As the code won't check Rows 3+ if no valid condition is met on Row2.
So when i talk it out it would sound like this...excuse my novice...
If Worksheets("PEF").Cells(r, 15) is not blank Then r = 2 Else
If Worksheets("PEF").Cells(r, 15) is not blank Then r = 3 Else...and so on until the end of the table is reached.
Thanks for any advice you may have!

[@syntaxbyte]

@@sarahsardella2927 If you just want to find the last non-blank cell can I suggest just using Range("a1").End(xlDown).Row?

[@sarahsardella2927]

@@syntaxbyte i would need the code to assign the row number based on the last non-blank cell found, if not found, needs to continue checking each row/cell until the table is finished. I've been stuck on this for days.

[@syntaxbyte]

@@sarahsardella2927 That code will find the row number of the first non-blank next to the range specified. It's the same selecting a1 and hitting ctrl+down. I think I got confused you want to find the last non-blank, not the first. So you can try the xlup trick... Range("a1000000").End(xlUp).Row