VBA Help

[EBern]

The below VBA for fixing a worksheet, formatting and adding new rows between different values in Column B works but does not run for the whole worksheet. Am I missing something? Would appreciate any help I get.

Thanks






Sub SORTINGandFORMATTING()
'
' SORTINGandFORMATTING Macro
'

'
Columns("A:A").Select
Application.ScreenUpdating = False

Selection.NumberFormat = "0"
Columns("A:B").Select
Selection.Replace What:="yellow", Replacement:="Yellow", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWorkbook.Worksheets("Test_28-06-2015").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Test_28-06-2015").Sort.SortFields. _
Add Key:=Range("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Test_28-06-2015").Sort
.SetRange Range("A:B")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.ScreenUpdating = False
'
Dim iRow As Integer, iCol As Integer
Dim oRng As Range

Set oRng = Range("b1")

iRow = oRng.Column
iCol = oRng.Column

Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'

End With
End Sub

[mehmetcik]

Your macro will stop at the first empty cell.

Please Login or Register  to view this content.

Normally we find the last used cell in a column and loop until we reach that row.

eg for column 2:

Please Login or Register  to view this content.

Please Login or Register  to view this content.