VBA Help: Using *Insert Row* to adjust distance between random distance of Row A to cell containing "ID #"

TransRlucent

New Member
Joined
Dec 12, 2018
Messages
2
I'm a bit new to VBA. But, I've gotten pretty far into this project at work. However, I've come across a logic problem that I don't have a knowledge base wide enough to complete:
The problem goes like this:

I have many Work books that I need to export to a master WB one at a time (this wb then processes the information through a large array of logic gates) which then are printed off in a summary of the information (this part is already done). However, in order to make the needed training for this workbook minimal, I'd like to incorporate a macro that goes into a folder (where the wbs in need of processing will go) to which the macro then cycles through each wb one after another, processes the information, and prints each of the processed and summarized information out as separate forms.

Most of this is done. However, the part I'm stuck on is the cycling through worksheets in each workbook and adjusting the margins of the top of the ws to a max of 4 rows distance between a cell containing the text "ID #" and the top of the ws (row A).

I also only have a testing copy of my macro (the big cats told me I can't take my work home):

Code:
Code:
Sub TEST_LOOP()
Dim rng     As Range, _
    rng1    As String

Range("A1").Select
Set rng = Cells.Find(What:="ID #", After:=ActiveCell, LookIn:=xlFormulas, _
            lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
If rng = 1 Then
    rng1 = rng.Address
    Do
        rng.Offset(-1, 0).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        Set rng = Cells.FindNext(rng)
    Loop While Not rng Is Nothing And rng.Offset(-1, 0).Address <> rng1
End If
End Sub

Again, I'm new to this so this is just a patchwork of other peoples codes that's close to what I want to do.

The worksheets I need to measure sometimes come in with 1, 2, or 3 rows between it and the row containing "ID #", so I would need a macro that adjusts that distance to 4 no matter if it is 1, 2, or 3 in the unaltered version.
I know this is an If Then command. But for some reason I can't wrap my head around how to find a distance and then tell the macro to make the distance 4.

Thank you in advance to any insight.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
So I changed your code so that it's a little simpler.

Code:
Sub InsertRows()


'Declare the Variables
Dim SearchRng As Range
Dim StrToFind As String
Dim WrkSht As Worksheet


'Loop through each worksheet in the ACTIVEWORKBOOK
For Each WrkSht In ActiveWorkbook.Worksheets


    'Activate the current worksheet
    WrkSht.Activate
    
    'Find the Particular String
    Set SearchRng = Cells.Find(What:="ID #")
                
    'If we didn't find that string don't do anything
    If Not SearchRng Is Nothing Then
       
       'Store the row number
       RowNum = SearchRng.Row
       
       'Insert four rows above the the cell that contains the string we need to find.
       For i = 1 To 4
           SearchRng.EntireRow.Insert
       Next i
       
    End If
    
Next WrkSht


End Sub

So all this code will do is loop through all the worksheets in the active workbook, find the string you specified, and then if it is found it will insert 3 rows above it. This means if it was at row 6 it would be at row 10 now.

Let me know if you have any questions :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top