maghari
02-25-2021, 12:13 PM
hello
I have this code inserts the rows before the row of total , but what I want insert based on selected on combobox from the userform based on column a with the formulas and the formatting and borders then copy the values from texetboxes to column a,b,c into the inserted a new row
Private Sub ComboBox1_Change()
Dim ws As Worksheet, Rng As Range, Sel As Variant
Set ws = Sheets("Task List")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
Set Rng = ws.Columns(1).Find(Sel, lookat:=xlWhole)
If Not Rng Is Nothing Then
Call InsertBlankRowsBasedOnCellValue
End If
End If
End Sub
Sub InsertBlankRowsBasedOnCellValue()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Col = "a"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "TOTAL" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
please check my file I put more explanation
I have this code inserts the rows before the row of total , but what I want insert based on selected on combobox from the userform based on column a with the formulas and the formatting and borders then copy the values from texetboxes to column a,b,c into the inserted a new row
Private Sub ComboBox1_Change()
Dim ws As Worksheet, Rng As Range, Sel As Variant
Set ws = Sheets("Task List")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
Set Rng = ws.Columns(1).Find(Sel, lookat:=xlWhole)
If Not Rng Is Nothing Then
Call InsertBlankRowsBasedOnCellValue
End If
End If
End Sub
Sub InsertBlankRowsBasedOnCellValue()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Col = "a"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "TOTAL" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
please check my file I put more explanation