VBA to insert row / colum

[philhowell]

Hi guy,

can't believe its been a year since i last posted on here....

i have been asked to write 4 seperate VBA modules;

1. insert a row above the cell selected
2. insert a row below the cell selected
3. insert a colum to the left of the cell selected
4. insert a colum to the right of the cell selected


any help is grrr8
Phil

 

[Greg Truby]

...[FONT=&quot]how VBA automatically insert a row on every 9000 rows start from Range "B2"? ...[/FONT]

Please take another look at post #7 (which is the same question as yours) and post #8 which answered that question. Let us know if you require assistance in applying the information provided.

 

[Big_A]

Thx for the quick response
I already try that step
But it doesn't work
I still confused about that

Could u explain again the answer for my question??

I really appreciate your help

 

[Greg Truby]

...[FONT=&quot]how VBA automatically insert a row on every 9000 rows start from Range "B2"? For example: if i doing insert row manually, i must go to Range "B9001" (Count data from B2 to B9001 is 9000) ...[/FONT]

OK - so we start with the code Tom Urtis first provided:

Sub InsertMania()

'Insert row above active cell
ActiveCell.EntireRow.Insert

End Sub

We make a couple of small edits:

Sub InsertEvery9K()
 
Range("A9000").EntireRow.Insert
Range("A18000").EntireRow.Insert
Range("A27000").EntireRow.Insert
Range("A36000").EntireRow.Insert
 
End Sub

We run this and see if the result are what we want. Assuming they are; we then take what cazicss provided:

Function AddRowEvery96()

Const MaxRows As Integer = 10000 'my max allowed rows
Dim row As Integer 'stores current row

'start at the first increment and loop through
'increments of 96 up until our max number of rows
row = 96
Do

'---CODE TO INSERT ROW HERE---'
row = row + 96
If row > MaxRows Then Exit Do
​

Loop
​

End Function

Now there is a problem with this which is that functions should not go around changing the worksheet. (It's actually forbidden in UDF's.) On top of that, I would rework the syntax to use a For/Next instead of a Do/Loop. So something like so:

Sub InsertEvery9K_theSequel()
 
    Const lngStartRow As Long = 2
    Const lngRowsToSkip As Long = 9000
 
 
    Dim lngRowCntr As Long, lngMaxRows As Long
 
    On Error GoTo Error_Insert9K
    Application.ScreenUpdating = False
 
    '// using column "A" here, but use appropriate column
    '// or use something like USEDRANGE to get last row
 
    If Len(Range("A" & Rows.Count).Formula) <> 0 Then
        Let lngMaxRows = Rows.Count
        'this will raise an error when attempt to insert a row
    Else
        Let lngMaxRows = Range("A" & Rows.Count).End(xlUp).Row
    End If
 
    For lngRowCntr = lngStartRow To lngMaxRows Step lngRowsToSkip
 
        Range("A" & lngRowCntr).EntireRow.Insert
        Application.StatusBar = "Inserting Row: " & lngRowCntr
    Next lngRowCntr
 
GoSub CleanUp
Exit Sub
 
CleanUp:
'¨¨¨¨¨¨¨
    Application.StatusBar = False
    Application.ScreenUpdating = True
 
Return
 
Error_Insert9K:
'¨¨¨¨¨¨¨¨¨¨¨¨¨¨
    GoSub CleanUp
    MsgBox "Could not complete all insertions" & vbCr & vbCr & _
           "Stopping on row " & lngRowCntr, vbExclamation, "Error - no more blank rows"
 
End Sub

 

[carcharoth2554]

Hi Guys,

I have a small amount of programming knowledge but relatively new to VB, just started looking this week. I have been asked to improve a working spreadsheet (Excel 2010) VB code. It currently works as follows:

1 - User opens new worksheet
2 - User copies over formatting template
3 - User fills in data fields
4 - User goes to 'Summary' worksheet
5 - User clicks 'Update' command button and actions the following code

"Sub HyperLink_SheetsToIndex()

' Local Variables
Dim wks As Worksheet
Dim rngLinkCell As Range
Dim strSubAddress As String, strDisplayText As String

' Step 1 : Loop through all worksheets
' 1a : Clear all current hyperlinks
Worksheets("Summary").Range("A1", "A99").ClearContents
' 1b : Create Linked index list
For Each wks In ActiveWorkbook.Worksheets
Set rngLinkCell = Worksheets("Summary").Range("A99").End(xlUp)
If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)

strSubAddress = "'" & wks.Name & "'!A1"
strDisplayText = wks.Name
Worksheets("Summary").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
Next wks


End Sub"

The idea behind the summary sheet is Columns "B" to "J" simply contain referencing formula to the other worksheets (creating a summary) and Column "A" is populated with indexed links to those worksheets based on the worksheet name and kept up to date by the "Update" command button. The problem this code creates is that when a new worksheet is created, the code only updates Column "A" and I cannot find a good way to integrate into the code a sub to make the rows with formula match those with a link in "A". It tried inserting a copy/insert function before the linked index is created and after it is created but the results were either creating a new row for each click of the "Update" button or a new row for each loop >.>

Sub Copyinsert()
'
' Copyinsert Macro
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B3:J3").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("B2").Select
End Sub

I am constricted as I have been told that no data/formula are allowed to exist on lines where there are no links in Col "A" and equally having all data in one sheet and issuing reports is unacceptable. I think I am close but just can't quite get there myself. I hope I have explained myself fully and would appreciate any help.

Thank you.

 

[Samiksha]

Sub InsertMania()

'Insert row above active cell
ActiveCell.EntireRow.Insert

'Insert row below active cell
ActiveCell.Offset(1).EntireRow.Insert

'Insert column to the left of the active cell
ActiveCell.EntireColumn.Insert

'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert

End Sub

How can I to put the default text in inserted new row

 

[Greg Truby]

Sub InsertAndAssignOneValue()

    
    Dim lngRow  As Long
    
    
    Let lngRow = ActiveCell.Row + 1
            
    '// insert a row below wherever we are
    ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown
    
    '// add some text into column B of the new row
    Cells(lngRow, 2) = "I'm a default value"


End Sub


Sub InsertAndAssignSeveralValues()
    
    Dim lngRow  As Long
        
    Let lngRow = ActiveCell.Row + 1
            
    '// insert a row below wherever we are
    ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown
    
    '// add some text into columns A:F of the new row
    Cells(lngRow, 1).Resize(, 6) = Array("Ape", "Bear", "Coyote", "Dog", "Eagle", "Falcon")


End Sub

 

[Rick Rothstein]

Sub InsertAndAssignOneValue()

    
    Dim lngRow  As Long
    
    
    Let lngRow = ActiveCell.Row + 1
            
    '// insert a row below wherever we are
    ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown
    
    '// add some text into column B of the new row
    Cells(lngRow, 2) = "I'm a default value"


End Sub


Sub InsertAndAssignSeveralValues()
    
    Dim lngRow  As Long
        
    Let lngRow = ActiveCell.Row + 1
            
    '// insert a row below wherever we are
    ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown
    
    '// add some text into columns A:F of the new row
    Cells(lngRow, 1).Resize(, 6) = Array("Ape", "Bear", "Coyote", "Dog", "Eagle", "Falcon")


End Sub

Since you calculated the inserted row and stored it in the lngRow variable, why not use it to simplify the code? Replace this...

ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown

with this...

Rows(lngRow).Insert Shift:=XlDirection.xlDown

And since Shift is the first argument that Insert has, there is no need to specify its name. Also, I am not exactly sure what you gain by specifying xlDirection in order to get to the built-in xlDown constant. Given that, the code line can be simplified further...

Rows(lngRow).Insert xlDown

Further, since the only direction rows can be shifted for an entire row, there is actually no need to specify the shift direction at all. Given that, this simple code line would work...

Rows(lngRow).Insert

 

[Greg Truby]

Since you calculated the inserted row and stored it in the lngRow variable, why not use it to simplify the code? Replace this...

ActiveCell.Offset(1).EntireRow.Insert Shift:=XlDirection.xlDown

with this...

Rows(lngRow).Insert Shift:=XlDirection.xlDown

And since Shift is the first argument that Insert has, there is no need to specify its name. Also, I am not exactly sure what you gain by specifying xlDirection in order to get to the built-in xlDown constant. Given that, the code line can be simplified further...

Rows(lngRow).Insert xlDown

Further, since the only direction rows can be shifted for an entire row, there is actually no need to specify the shift direction at all. Given that, this simple code line would work...

Rows(lngRow).Insert

Certainly I could have been more succinct. I just tend to code rather verbosely and I was just whipping that up right quick, so I just used my "default" style (for the most part). In general I would never use any reference where I wasn't rock solid sure of the result, i.e. using the call to CELLS with specifying the parent set my teeth on edge. More often than not, I'll qualify a constant since my projects frequently involve referencing a variety of libraries. Again, just following habits.

The use of the ActiveCell.Offset instead of Rows bit was just so it'd look more like what Tom posted all those years ago.

 

[Rick Rothstein]

Certainly I could have been more succinct. I just tend to code rather verbosely and I was just whipping that up right quick, so I just used my "default" style (for the most part). In general I would never use any reference where I wasn't rock solid sure of the result, i.e. using the call to CELLS with specifying the parent set my teeth on edge. More often than not, I'll qualify a constant since my projects frequently involve referencing a variety of libraries. Again, just following habits.

The use of the ActiveCell.Offset instead of Rows bit was just so it'd look more like what Tom posted all those years ago.

My post wasn't meant as a criticism of your code... what I was trying to do is show the reader a step-by-step method to simplify the code... I used your code line because it was correctly constructed and available. I am sorry if it seemed I was doing otherwise.

 

[Greg Truby]

No worries, Rick. And actually, your post prompted me to try a little experiment which proved that my inclusion of the Shift argument was indeed quite pointless. If one specifies .EntireRow, then — as you articulated — the only possible shift direction is down. But out of curiosity I tried

Sub foo()    
    With ActiveCell.EntireRow
        .Insert Shift:=XlDirection.xlToLeft
        .Insert Shift:=XlDirection.xlToRight
        .Insert Shift:=XlDirection.xlUp
    End With
End Sub

All yield the same result as xlDown or no argument at all would yield. And, since none raises an error, it looks like the argument is simply ignored in this context.