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]

Hi and welcome to MrExcel.
Try this:

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
    If [ECL_Simulations].Value <> "" Then
      [ECL_Simulations].Copy
      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
    End If
    Range("ID").Value = i
    Application.StatusBar = ([Num_Sims] - i)
  Next i

End Sub

 

[jastisid]

Hi Dante,

Thanks for the prompt response. I tried your suggested VBA code, however, I am getting a "Type Mismatch" error. Also, one other thing with the original VBA is that it ignores any data after the 1st row.

Please see attached the sample images. In the "Input Tab", I have assigned "ID" to each unique ID, and in the "Output Sheet", it runs all the simulations and prints the results. However, as you will notice for ID = "1", there are two unique IDs, but the VBA prints only the data relating to first unique ID. Any suggestions to print all unique ID data?

Also, when I mentioned skip blank rows, if you change the ID = "4", there is only one unique ID. Therefore, I want the VBA to skip the remaining rows and move to the next ID.

Again appreciate your help!

Thanks

 

[DanteAmor]

I am getting a "Type Mismatch" error.

In which line of the macro?

 

[jastisid]

In this line

If [ECL_Simulations].Value <> "" Then

 

[jastisid]

Hi - Just checking if anyone else has a solution?

 

[offthelip]

try something like this, where I am using two variant arrays to get rid of the blank rows (untested)

For i = 1 To Range("Num_Sims").Value
inarr = [ECL_Simulations]
Dim outarr() As Variant
rowno = UBound(inarr, 1)
colno = UBound(inarr, 2)
ReDim outarr(1 To rowno, 1 To colno)
 indi = 1
 For k = 1 To rowno
 If inarr(k, 1) <> "" Then
  For j = 1 To colno
  outarr(indi, j) = inarr(k, j)
  Next j
 indi = indi + 1
 Next k
 Sheets("ECL output - per Loan basis").Range(Cells(13, 2), Cells(13 + indi, 2 + colno)) = outarr
 Next i

 

[Fluff]

Cross posted VBA - Skip blank rows in loop and paste multiple rows in a simulation

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[DanteAmor]

In an exercise I did with your macro, first copy 2 records, and paste them in rows 14 and 15, the second cycle copies 2 records and paste them in rows 15 and 16, that is, it overwrites the record of row 15 .
If what your macro does is correct, then with this you only copy values with data:

Sub Output()
  Dim Temp1 As Integer
  Dim i As Integer
  
  Application.ScreenUpdating = False
  
  [Output_Consol].ClearContents
  Temp1 = Range("ID").Value
  For i = 1 To Range("Num_Sims").Value
    [Sample_Simulations].SpecialCells(xlCellTypeFormulas, 1).Copy
    Range("ID").Value = i
    Sheets("Output").Select
    Sheets("Output").Cells(13 + i, 3).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Application.StatusBar = ([Num_Sims] - i)
  Next i

End Sub

But if you don't want to overwrite then try the following:

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].SpecialCells(xlCellTypeFormulas, 1).Copy
    Range("ID").Value = i
    Sheets("Output").Range("C" & lr).PasteSpecial xlPasteValues
    lr = Sheets("Output").Range("C" & Rows.Count).End(3).Row + 1
    Application.StatusBar = ([Num_Sims] - i)
  Next i
  
  Application.StatusBar = False
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[jastisid]

Hi Dante - Thank you very much, the second code is exactly what I was looking for. The code works like a charm. The only other question I had was in case if any of the input data was text let's say "A", then I think the code skips the column. I think it is to do with this statement in the second code " [Sample_Simulations].SpecialCells(xlCellTypeFormulas, 1).Copy". Is there anyway to avoid this. Please see sample snapshots below.

In the sample below if I change the Sample Data (2) to "A", then it skips the column in the output sheet.