CODEWITHSUNDEEP
excelvba
TA Arjun
TA Arjun

Reputation: 25

Add a new row and Format the new row added

I have a sheet where the number of rows is dynamic. I am trying to add a macro which adds a new row after each active row and it should add the text "No Show" under column C of each new row added and the it should add the cell value E5 under D column.

Here is the example below:

Current Sheet:

enter image description here

After the Macro: (Test in E5 is Holiday)

enter image description here

I have a macro to add new empty rows but not sure how to integrate the other pieces of it.

Sub Insert_Blank_Rows()

Selection.End(xlDown).Select
Do Until ActiveCell.Row = 1
    ActiveCell.EntireRow.Insert shift:=xlDown
    ActiveCell.Offset(-1, 0).Select
Loop

End Sub

Upvotes: 2

Views: 480

Answers (3)

Ferdinando
Ferdinando

Reputation: 964

if i understood your question you can:

in this example i suppose that you have in cell E5 the text Holiday.

i tried at no change your code

EDITED THE IMAGE and CODE

(because before i used E1 cell and i don't write AB... into new column)

BEFORE EXECUTE THE MACRO

enter image description here

AFTER MACRO

enter image description here

Sub Insert_Blank_Rows()
Dim text, textCell_E5 As String
Dim myRow As Long

text = "no Show" ' this thext goes into column C
textCell_E5 = Cells(5, 5) ' Holiday
ActiveSheet.Range("A1").Select ' or cells(1,1).Activate

Selection.End(xlDown).Select
myRow = ActiveCell.Row + 1
Cells(myRow, 1).Offset(0, 2) = text
Cells(myRow, 1).Offset(0, 3) = textCell_E5
Cells(myRow, 1).Offset(0, 0) = Cells(myRow, 1).Offset(-1, 0) 
Cells(myRow, 1).Offset(0, 1) = Cells(myRow, 1).Offset(-1, 1)

  Do Until ActiveCell.Row = 1

      ActiveCell.EntireRow.Insert shift:=xlDown

      myRow = ActiveCell.Row ' get the current row
      Cells(myRow, 1).Offset(0, 2) = text ' write into column C the no Show
      Cells(myRow, 1).Offset(0, 3) = textCell_E5 ' add Holiday Text
      Cells(myRow, 1).Offset(0, 0) = Cells(myRow, 1).Offset(-1, 0) 'write into column A (new row)
      Cells(myRow, 1).Offset(0, 1) = Cells(myRow, 1).Offset(-1, 1) ' write into column B (new row)

      ActiveCell.Offset(-1, 0).Select
  Loop

End Sub

I Tried the code and works.

Hope this helps

Upvotes: 0

JohnyL
JohnyL

Reputation: 7132

Sub FFF()
    Dim r&, vE5
    vE5 = [E5]: r = Cells(Rows.Count, 1).End(xlUp).Row + 1
    While r > 1
        Rows(r).Insert
        Cells(r, 1).Resize(, 4) = Array(Cells(r - 1, 1).Resize(, 2), "No Show", vE5)
        r = r - 1
    Wend
End Sub

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

loop backwards:

Option Explicit

Sub Insert_Blank_Rows()
    Dim iRow As Long
    Dim myText As String

    myText = Range("E5").Text
    With Selection
        For iRow = .Rows.Count To 1 Step -1
            .Rows(iRow + 1).EntireRow.Insert shift:=xlDown
            With .Rows(iRow + 1)
                .Range("A1:B1").Value = .Offset(-1).Range("A1:B1").Value
                .Range("C1:D1").Value = Array("No Show", myText)
            End With
        Next
    End With
End Sub

Upvotes: 0

Related Questions