0

Here I dynamically take 2 values "StartRng" --> where to add the rows & "RowCount" --> No of rows to be added.

Now I have starting range as "Beta" & RowCount as 5 which is static now. I need to actually find the value "Beta" and assign this as startRng without using the inputBox which I'm using now. Then where "Beta" is present I need to add 5 rows below and focus on the first added row instead of the last.

Below is the code which I use.Is there any simple adjustments to be done so that I can get my desired output. 

Sub BlankLine()
'Updateby20200123
'Author David Nithin Rajan
Dim Rng As Range
Dim WorkRng As Range
Dim StartRng As String
Dim RowCount As Integer
On Error Resume Next
xTitleId = "AddRows"
StartRng = Range("G1").Value --> use this code for dynamic value setting
RowCount = Range("I1").Value --> use this code for dynamic value setting
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", AddRows, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.Columns(1)
xLastRow = WorkRng.Rows.Count
Application.ScreenUpdating = False
For xRowIndex = xLastRow To 1 Step -1
Set Rng = WorkRng.Range("A" & xRowIndex)        
If LCase(Rng.Value) = LCase(StartRng) Then
Rng.Offset(0, 0).EntireRow.Offset(1).Resize(RowCount).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove_ ActiveCell.EntireRow.Copy
ActiveCell.Offset(0).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End If
If LCase(Rng.Value) <> LCase(StartRng) Then
MsgBox ("Selected cell value ( " + Rng.Value + " ) must be same as 'Insert Row After' column data (" + StartRng + ")!!!")
End If
Next
Application.ScreenUpdating = True
End Sub

I'm a novice in excel VBA. The code above is courtesy of google search and basic output oriented editions.

braX
  • 11,506
  • 5
  • 20
  • 33
DNR
  • 5
  • 4
  • You can use the `Find` method to find the text. That returns a range which you can then expand using the `Resize` method. – SJR Jan 23 '20 at 11:47
  • I will try that. thanks for the assist. how about the focus? How can I focus after adding the rows to the first added row? – DNR Jan 23 '20 at 12:06
  • What do you mean by adding rows to the first added row? The first row you add will be `offset(1)` from the cell containing "Beta", no? – SJR Jan 23 '20 at 12:16

1 Answers1

0

I'm not completely sure what you're trying to do, but maybe this will start you off. 

Sub x()

Dim rFind As Range, r As Range

With Sheet1.Columns(1)
    Set rFind = .Find(What:="Beta", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) 'find beta
    If Not rFind Is Nothing Then                                          'if found
        rFind.Offset(1).Resize(5).Insert shift:=xlDown                    'insert 5 cells underneath
        MsgBox rFind.Offset(1).Address                                    'address of cell under beta
        Set r = rFind.Offset(1)                                           'or can assign to range variable
    End If
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Hey SJR, this kind of solves the issue of adding the rows, yes. But my main problem is selecting the cell which was added first in the 5 newly inserted row. That is, after adding the 5 rows, the active cell should be the first cell in the first added row. – DNR Jan 23 '20 at 12:42
  • 1
    Hey SJR , Thanks for ur simple solution -- I changed the code like this. – DNR Jan 23 '20 at 12:51
  • addr = rFind.Offset(1).AddressActiveWorkbook.Worksheets("Sheet1").Range(addr).Activate – DNR Jan 23 '20 at 12:54
  • Instead of message box I stored the value in variable and used that variable to bring focus on that cell. – DNR Jan 23 '20 at 12:57
  • Glad it worked. You should read this though as there is rarely any need to select or activate anything https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Jan 23 '20 at 13:03