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
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