For/next loops and how to enter

NoSparks · #1 08-15-2017, 07:02 AM

Have no idea as to why Excel stopped working and Windows finding a problem other than perhaps a corruption within the Excel installation.
I've never used it but believe there is a "Repair" option of some sort.

Anyway, lets start from scratch...
Open your Excel to new blank workbook with at least 2 sheets, leave the sheet names at the default Sheet1 and Sheet2.
On Sheet1 put in some data that reflects what you describe in your original post with some column B cells containing only a single zero.
Bring up the Visual Basic for Applications (VBA) Environment. (Short cut to this is Alt + F11)
From the menu bar, Insert > Module
Type (or paste) this into the Module that was just inserted.

Code:

Sub Testing()
    'declare all variables
    Dim lastrow As Long
    Dim i As Long
    Dim writerow As Long

'the first row to write to
writerow = 1

With Sheets("Sheet1")
    'establish last cell with data in column B
    lastrow = .Range("B" & Rows.Count).End(xlUp).Row
    'the rows to loop through
    For i = 2 To lastrow
        ' do what you want regarding this row
        ' BUT **** DO NOT DELETE IT ****
        ' for that you must work from the bottom up using step -1
        If .Range("B" & i).Value = 0 Then
            .Range("B" & i).EntireRow.Copy Sheets("Sheet2").Range("A" & writerow)
            writerow = writerow + 1
        End If
    Next i
End With
End Sub

Close the VBA environment.
Close and save the workbook as an Excel Macro-Enabled Workbook with the .xlsm file extention.
Re-open the just saved workbook.
Bring up the Macro dialogue box, View Macros (Alt+F8 is shortcut)

Run the macro.
See that rows where column B is 0 were copied to Sheet2.

Hopefully the VBA instructions along with the added comments will help you follow what the macro does.
Putting your cursor anywhere within the macro and using the F8 key will step one line at a time through the macro.

Hope this helps.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Searching Arrays without Loops?	ptmuldoon	Word VBA	3	12-13-2014 11:21 AM
HTML in email loops endlessly	rbtroj	Outlook	0	05-19-2011 12:29 PM
Enter + Alt + Enter is not working inside the text box! why????	Jamal NUMAN	Word	3	04-17-2011 05:35 PM
Powerpoint loops, but not to beginning	imeister	PowerPoint	1	02-02-2011 02:05 PM
loops within presentations	supateach	PowerPoint	1	11-23-2010 04:42 AM