I'm very interested in VBA Loops but have come up against a small but annoying problem.
I'm Basically looping a range on sheet 2 for non blanks then moving the non blanks to sheet 1.
The issue is the range between the 2 sheets are different (Much less non blanks than blanks)
On sheet 1 I have multiple ranges and they overwrite the range below as the code is using the full range in sheet 2
So that said sheet 2 loop i = 3 to 54 But for sheet 1 I just want to use 3 to 23 ..... Code below
Code
Sub Test()
Dim i As Integer, j As Integer
Dim Mysheet As Worksheet, myOtherSheet As Worksheet
Set Mysheet = Sheet2
Set myOtherSheet = Sheet1
With Sheet2
Ndt = Application.Range("Testing").Cells.SpecialCells(xlCellTypeConstants).Count
End With
If Ndt > 16 Then
MsgBox "Too Many names!!"
Call Clear
Exit Sub
Else
End If
Call Clear
j = 3
For i = 3 To 54
If Mysheet.Cells(i, 2).Value <> "" Then
myOtherSheet.Cells(j, 2).Value = Mysheet.Cells(i, 2).Value
j = j + 1
End If
Next i
End Sub