Hi All,
I Copy data into a tab, i have few macros that each put in a formula for eg
1) Macro to adjust Columns
2) Macro to identify bold text, it then copy and pastes the formulas which goes on to deleting the bold text.
3) I use a rank formula to list the £earnings in rank.
The issue i have is the following:
1) When i copy the data over the number of rows vary - at the bottom of the table is merge cells which i usually delete in the macro but thats if its according to the vba code i.e it specifies a certain row
2) Because the number of rows vary, the formula gets copied to less rows and misses out the data for me
Help
How can i counter attack this in VBA so the number of Rows doesnt cause an issue
This is an example of the code
Thank you
I Copy data into a tab, i have few macros that each put in a formula for eg
1) Macro to adjust Columns
2) Macro to identify bold text, it then copy and pastes the formulas which goes on to deleting the bold text.
3) I use a rank formula to list the £earnings in rank.
The issue i have is the following:
1) When i copy the data over the number of rows vary - at the bottom of the table is merge cells which i usually delete in the macro but thats if its according to the vba code i.e it specifies a certain row
2) Because the number of rows vary, the formula gets copied to less rows and misses out the data for me
Help
How can i counter attack this in VBA so the number of Rows doesnt cause an issue
This is an example of the code
VBA Code:
Sub BoldFormulaDeleteBoldHeadings()
'
' BoldFormulaDeleteBoldHeadings Macro
'
Application.ScreenUpdating = False
Range("A6").Select
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = "=isbold(RC[5])"
Range("A6").Select
Selection.Copy
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Range("E120").Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("A:A").Select
Range("A5").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("F5").Select
Selection.End(xlDown).Select
Rows("121:125").Select
Range("F125").Activate
Selection.Delete Shift:=xlUp
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveSheet.Range("$A$5:$I$120").AutoFilter Field:=1, Criteria1:="TRUE"
Rows("6:6").Select
Range("F6").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Range("$A$5:$I$120").AutoFilter Field:=1
ActiveWorkbook.Worksheets("Latest").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Latest").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("G5:G120"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Latest").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
Application.ScreenUpdating = True
End Sub
Thank you