To delete the blanks, just select the whole sheet and sort the data by the column that may have blanks.
Is there a way of copying for formatting across when using the rowns=rows format ?
Sub CLEANUP_S2()
'
' CLEANUP_Sheet2 Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F2:F9999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Rows("2:9999")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.NumberFormat = "@"
Columns("F:F").Select
Selection.NumberFormat = "General"
Cells.Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("F2:F9999") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Rows("2:9999")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
e.Rows(d).Value = i.Rows(j).Value
Thanks
My requirement is same. instead of 'TERM' i need to use a column value. could you please let me know how to do this?