Adding rows by value location, excel crash

Deverti

Board Regular
Joined
Sep 5, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
This probably isnt the the most appropriate solution to begin with but it gets the job done, almost.

Thing is, "BufferBelow" works as intended while "BufferAbove" leads excel into a crash.

What s the big difference? i dont understand ~

VBA Code:
Sub BufferBelow()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRb As Range
  
   For Each vRb In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRb.Value
         Case "Aufwand", "Ertrag"
            vRb.Offset(0, 1).EntireRow.Insert
      End Select
   Next vRb
End With
Application.ScreenUpdating = True
End Sub

Sub BufferAbove()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRa As Range
  
   For Each vRa In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRa.Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            vRa.Offset(0, -1).EntireRow.Insert
      End Select
   Next vRa
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You are offsetting by -1 column in the second code, there is no column to the left of column A so it errs.
I suspect that you meant to be offsetting by rows not columns (in both codes).
 
Last edited:

Deverti

Board Regular
Joined
Sep 5, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
oh duh, quickly added/removed some things for visibility beforehand and screwed that part up

was using the below and encountered the described crash. perhaps im still blind

VBA Code:
Sub BufferBelow()
...
           vRb.Offset(1).EntireRow.Insert
...

Sub BufferAbove()
...
           vRa.Offset(-1).EntireRow.Insert
...
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If what you have just posted is crashing then you need to post the entire code and the error message as that code is offsetting by rows (other than the variables name is different).
 

Deverti

Board Regular
Joined
Sep 5, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I simply uploaded an example to this temporary file host. And ofc, let me know if i should upload it some other way

 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can't download a file as on my phone. Please post the full code as requested
 

Deverti

Board Regular
Joined
Sep 5, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oh i see. Not quite sure i follow about posting the entire code part since it hasnt changed but this is the Sub which is acting up.

Im not getting any error messages, it simply keeps running til it freezes.

VBA Code:
Sub AddSubTitleBufferA()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRa As Range
  
   For Each vRa In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRa.Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            vRa.Offset(-1).EntireRow.Insert
      End Select
   Next vRa
End With
Application.ScreenUpdating = True
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have any of those words in A1?

Btw you can remove both the With/End With lines as you aren't using them in the code.

I also think that you will probably need to loop from bottom to top as you are inserting rows but won't be able to test that until I get back in (a couple of hours).
 

Deverti

Board Regular
Joined
Sep 5, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
Oha, wasnt aware about "With ActiveSheet" being superfluous. And yes those words all do exist in Column A, several times.

Thanks for the help so far.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I asked about cell A1 specifically not the rest of the column ;), I'll explain about the With statement when I get back in. Anyway....

Try the code below but don't be surprised if errors as typed on my phone and obviously untested.

VBA Code:
Sub AddSubTitleBufferA()
Dim x as Long
For x = Range("A" & Rows.Count).End(xlUp).Row to 2 Step - 1
       Select Case Cells(x, 1).Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            Cells(x, 1).Offset(-1).EntireRow.Insert
        End Select
Next 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top