VBA - Skip blank cells in loop

[jastisid]

Hi there,

I have an excel template where I use the loop function to run several simulations and paste the results in an output sheet, and I use the following code. However, I have a situation now where in some iteration one or two rows can be blank, is there anyway to skip the blank rows while running the loop function, such that in the output sheet only rows with value are pasted?

Any help is much appreciated

Sub Output()
Application.ScreenUpdating = False

[ECL_Consol].ClearContents

Dim Temp1 As Integer
Dim i As Integer

Temp1 = Range("ID").Value

For i = 1 To Range("Num_Sims").Value

[ECL_Simulations].Copy
Range("ID").Value = i

Sheets("ECL output - per Loan basis").Select
Sheets("ECL output - per Loan basis").Cells(13 + i, 2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.StatusBar = ([Num_Sims] - i)


Next i

End Sub

 

[DanteAmor]

We can return to the original Copy, the detail was to find the last row with data in the "Output" sheet
Try this:

Sub Output()
  Dim Temp1 As Integer
  Dim i As Integer, lr As Long
  
  Application.ScreenUpdating = False
  
  [Output_Consol].ClearContents
  lr = 14
  Temp1 = Range("ID").Value
  
  For i = 1 To Range("Num_Sims").Value
    [Sample_Simulations].Copy
    Range("ID").Value = i
    Sheets("Output").Range("C" & lr).PasteSpecial xlPasteValues, , True
    lr = Sheets("Output").Range("C:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
    Application.StatusBar = ([Num_Sims] - i)
  Next i
  
  Application.StatusBar = False
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[jastisid]

Thanks you very much, that resolves my query, and the code works perfectly fine. Thanks again and appreciate you time!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.