Macro Help?

[balanceshift]

Hi Guys,

First post here! I have no idea what I'm doing with this forum or Exel, so apologies in advance!

I'm hoping you all can help me. I have some basic programming knowledge but not enough to be able to write a macro in Exel off the top of my head.

My boss at work has asked me to add a line break after every subtotal in a huge spreadsheet, so I'm hoping to have a macro:
- Search for the string "Total" in column B
- Add a line break below it (move down a cell, shift+space, ctrl+'+' ?)

Any assistance would be super muchly appreciated!

 

[balanceshift]

Holy crap I think I figured it out!

I just ran the below. Please tell me it makes sense?!

Option Compare Text
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
 For r = lr To 2 Step -1
   If InStr(Range("K" & r).Value, "AUD") And (Range("L" & r) = 0) Then Rows(r - 1 & ":" & r + 1).Delete
 Next r
End Sub

 

[Michael M]

Well done, minor tweak.....you don't need the brackets round the 2nd range

(Range("L" & r) = 0)

can be 

Range("L" & r) = 0

 

[Peter_SSs]

balanceshift

I understand that you have a working result to the original question. However, you said you had a huge sheet and if you have to do this task again, you might consider these approaches that are considerably faster. I'm assuming that your subtotals have been applied using Excel's built-in Subtotal feature.

Now it does depend on what you mean by "huge" and how many subtotal rows there are, but with my sample data of 200,000 rows my method 1 below is roughly 20 times faster than what you have used and method 2 is about 15 times faster. The speed increase is due to not having to check every row to see if it is a subtotal row but going directly to those rows.

So ..

Method 1
This method requires that one of the columns you are subtotaling (NOT Column B) contains no formulas, just data, other than the formulas in the subtotal rows.
In my example, that is column C. Change that if needed to suit your data.

As it stands, this code also inserts 2 blank rows before the Grand Total row. If that is a problem, an extra line in the code can fix it.

Sub InsertRows1()
  Columns("C").SpecialCells(xlFormulas).Offset(1).EntireRow.Insert
End Sub

Method 2
If method 1 is unsuitable (or just for interest) you could try this

Sub InsertRows2()
  Dim lr As Long
  Dim cell As Range

  Application.ScreenUpdating = False
  lr = Range("B" & Rows.Count).End(xlUp).Row - 1
  ActiveSheet.Outline.ShowLevels RowLevels:=2
  For Each cell In Range("B2", Range("B" & lr)).SpecialCells(xlVisible)
    cell.Offset(1).EntireRow.Insert
  Next cell
  ActiveSheet.Outline.ShowLevels RowLevels:=3
  Application.ScreenUpdating = True
End Sub

BTW, about how many rows/columns of data are you dealing with?

And which version of Excel are you using?