do while loop in VBA

Question

I have data that looks like the table shown below. The number of observations in this dataset varies each month. The columns remain the same. I would like to loop my code through each row until the row is empty. I think a do while loop would be appropriate, but I have not been successful in executing it thus far (of note, I am a complete VBA newbie.)

A couple of other notes: The only thing that will change as the code runs through each observation of data is the Range selected in line 2 (I will want to move down to the next row of observations) and the final range selected for the Paste Special step in the final line of the code (again, I will want to move down to the next row of observations with each iteration).

Sample Data:
Sex   Age Race    Total Cholesterol   HDL-Cholesterol Systolic Blood Pressure Treatment for High Blood Pressure   Diabetes    Smoker  
F 50  AA  300 90  200 Y   Y   Y   
M 55  AA  290 90  200 Y   Y   Y   
F 50  AA  300 90  200 N   N   N

Code that I need to loop through each non-empty row:

Sub ASCVD()

    Sheets("Sheet1").Select
    Range("A2:I2").Select
    Selection.Copy
    Sheets("Omnibus").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


End Sub

Thank you very much in advance for your help!!!

This Stack Overflow Excel-VBA documentation on finding the last used row will help. Once you get the last row, you can loop from 3 to LastRow — Scott Holtzman
– Scott Holtzman, Commented Dec 20, 2016 at 16:16

Juuso Nykänen · Accepted Answer · 2016-12-21 08:19:59Z

On second thought, what you are really asking is how to use a Do While loop:

Sub ASCVD()

    Dim row As Integer
    row = 2

    Do While ThisWorkbook.Sheets("Sheet1").Cells(row, 1) <> "" 'Loop until first cell is empty

        ThisWorkbook.Sheets("Sheet1").Range("A" & row & ":I" & row).Select
        Selection.Copy
        Sheets("Omnibus").Select
        Range("C3").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        Range("B13").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("J" & row).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        row = row + 1
    Loop


End Sub

The code loops throw rows 2, 3, 4, 5 .... and stops when it finds a row where the first cell is empty

Juuso Nykänen · Accepted Answer · 2016-12-21 08:09:17Z

Create an example of your desired end result, it is extremely unclear what you want to achieve at the moment. (Right now you are only describing a mess of implementation, not what the big picture is)

Anyway I am assuming all you want to do is transpose your data, this code does the job:

Sub ASCVD()

    Dim Data() As Variant
    Dim nrow As Integer

    Data() = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Value
    nrow = UBound(Data(), 1)

    ThisWorkbook.Sheets("Omnibus").Activate
    DoEvents 
    'Previous two lines needed so that the .Range(Cells(3.3), ... part works below

    ThisWorkbook.Sheets("Omnibus").Range(Cells(3, 3), Cells(16 + 2, nrow + 2)) = Application.WorksheetFunction.Transpose(Data()) 
    'Cells(rownumber, columnnumber). Cells(1,1) is cell A1
    'Cells(3, 3) is same as cell C3.
    'Cells(16 + 2, nrow + 2) in your example case will be cell F18, the last cell of your data.
    '+2 because you want to start from C3, meaning all your data is shifted two cells down and two cells right

End Sub

Collectives™ on Stack Overflow

do while loop in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related