Forum Discussion
Use VBA to Autofill a Row until the end of the number of data in another row
Hi Haytham Amairah,
I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from:
Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Selectto:
Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Hi Mike,
Please try this one:
Sub test()
ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
Selection.AutoFill Destination:=Range("N1:N" & Range("M" & Rows.Count).End(xlUp).Row)
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Hope that helps
Hi
I am trying to build a macro to unprotect sheet, autofill columns from above and protect sheet again. I am using below formula however it is giving error. Can anyone help please?
Sub sbUnProtectSheet()
ActiveSheet.Unprotect "9999"
Set SourceRange = ActiveSheet.Range("B10:H10")
Set fillRange = ActiveSheet.Range("B11:H39")
SourceRange.AutoFill Destination:=fillRange
ActiveSheet.Protect "9999", True, True
End Sub
Thanks
Anil
Hi,
It's better to post your question as a new conversation in the community.
If you can, provide us with a sample of the worksheet you're working on!
Regards
Hey Haytham,
I'm really hoping you can point me in the right direction as well.
I'm trying to do the same thing as OP, however I have blank rows above and to the left of my data and not sure if that is throwing it off. Below is a screenshot (had to switch all values to xxxx for privacy purposes), I want my Macro to create a new Column to the left of Column J in the screenshot, where a new Column J will be created that will house my formula beginning in the new Cell J6. I then want the Macro to extend this formula down the last row in any of the columns to the left of Column J, as they will all always be populated, however I have been tying everything to Column I in my troubleshooting.
Here is the snip of code that I know is causing the issue, with the problem rows likely being the three rows below my IF formula.
Sheets("Raw_Data").Select
ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "MEASURE OUTCOME"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A366")
ActiveCell.Range("A1:A366").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Met", _Are you able to help me re-write this code to have the Macro fill the IF formula down to the last row of data rather than just the last cell in my current selection? The number of rows will change on a daily basis.
Thank you!
Hi,
Sorry about the late reply!
I think you need this one:
Sub FillFormulaDown()
'To check if the column is already exist
If Application.WorksheetFunction.IsFormula(Range("J5").Offset(1, 0)) Then
'skip
Else
Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J5").Value = "MEASURE OUTCOME"
End If
Range("J5").Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
Selection.AutoFill Destination:=Range("J6:J" & Range("I" & Rows.Count).End(xlUp).Row)
ActiveCell.EntireColumn.AutoFit
End SubYou will find it in the attached workbook.
Please test it and tell me what you thought.
Hope that helps