Inserting partial column VBA

[keef2]

Hello,

I currently have this code that works to insert full rows. However, I was hoping to limit the inserting of the row to columns A:J.
Bonus points to see who can help me figure out that if I delete column C why my current code doesn't work?

I tried to add .resize(1,10) but for some reason I keep getting errors, maybe the location in which i try to add this function. Any guidance/help is appreciated as always!

As you can see after I hit the add row button my code output is this:

Schedule KEM WORKING.xlsm
	A	B		D	E	F	G	H	I	J	K	L
27	Job #:								Time:	7:00		25
28												26
29	Employees :											27
30												28
31												29
32												30
33	Job #:								Time:	7:00
34
35	Employees :
36
37
38
39												31
40												32
Schedule

Cell Formulas
Range	Formula
E27,E33	E27	=IFERROR(VLOOKUP(B27,Table2,2,FALSE),"")
E28,E34	E28	=IFERROR(VLOOKUP(B27,Table2,3,FALSE),"")
L27:L32,L40	L27	=L26+1
L39	L39	=L32+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C33:H33	Expression	=COUNTIF($C33,"*Job*")	text	NO
C27:H27	Expression	=COUNTIF($C27,"*Job*")	text	NO

Here is my current code:

Sub Add_Job()
    
    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet
    bot_row = act.Range("Z1")
    
    act.Rows(bot_row & ":" & bot_row + (5)).Insert Shift:=x1ShiftDown
    act.Range("A3:J8").Copy
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormulas
    Range("B" & bot_row & ":B" & bot_row + (5)).ClearContents
    
    Application.CutCopyMode = False
    
End Sub

 

[keef2]

I got it....

Here is the updated code:

Sub Add_Job()
    
    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet
    bot_row = act.Range("Z1")
    
    act.Range("A" & bot_row & ":J" & bot_row + (5)).Insert Shift:=xlShiftDown
    act.Range("A3:J8").Copy
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormats
    act.Range("A" & bot_row & ":J" & bot_row + (5)).PasteSpecial xlPasteFormulas
    Range("B" & bot_row & ":B" & bot_row + (5)).ClearContents
    
    Application.CutCopyMode = False
    
End Sub

 

[keef2]

The question regarding deleting column C is still not figured out yet. Any help is appreciated, thanks!

 

[keef2]

Dummy me i figured this out. When deleting column "c" my referenced cell for bot row got shifted i simply updated the reference cell Z1 after deleting and fixed my issue.