Macro to perform a sort...

[majordog]

I have something like an address book in excell spreadsheet. It has col's A through F. In beween every different business type(which is column B) I have five blank lines. I want to write a macro that will sort this sheet by business but retain the blank lines. For example:
A B C D E F
Justin Doggys 45 5676 2 2
Sam Doggys 4 45 5 4





Sarah Carmich 78 pins 3 3





Simon Business 12 jars 1 1
John Business 2 cans 2 2








Should look like after the sort:
A B C D E F
Simon Business 12 jars 1 1
John Business 2 cans 2 2




Sarah Carmich 78 pins 3 3




Justin Doggys 45 5676 2 2
Sam Doggys 4 45 5 4


Can anybody help me?

 

[majordog]

Sorry, I have the wrong info above, column B will only have a value in the first entry, any preceding entries would be blank...

 

[majordog]

I have started with some code...still looking for help:

Dim intRowCount As Integer 'Declare variable which holds total row count
Dim intRow As Integer 'Declare variable to hold row number of business column which carries value
Dim myArray() As Integer
Dim intRowACount As Integer

intRowCount = (ActiveSheet.UsedRange.Rows.Count + 5) 'Determine the number of rows which have values

For intRow = 5 To intRowCount
If ActiveSheet.Range(&quot;B&quot; & Format(intRow)).Value <> &quot;&quot; Then
intRowACount = intRow
For intRowCount = 1 To intRowACount
If ActiveSheet.Range(&quot;A&quot; & Format(intRowACount)).Value <> &quot;&quot; Then
intRowACount = (intRowACount + 1)
End If
Next intRowCount
End If
Next intRow

 

[TonyJollans]

Hi majordog,

Generally impatience doesn't get you a lot of help round here, but you look like you're trying. It's very quiet today - it's a holiday in the States.

Spreadsheets aren't designed to work well with blank lines. Depending on your data, your first row in each business type may change with the sort so you'll need to check them all after sorting and blank out some and fill in others. All in all, a bit messy, and I would fill in column B before sorting and clear it out again afterwards I think. To do this follow the steps below. If you want a macro then the best thing to do is to record yourself doing it.

1. In row 2 of an empty column (column G) put the formula =IF(ISBLANK(B2),C1,B2)
2. Copy the formula down column G from G1 down as far as your data goes plus 5 rows. It will give you a #REF! error in row 1 but you can ignore that
3. Select Column G down as far as you have filled it and do Edit > Copy. Then Select cell B1 and do Edit > Paste Special > Values.
4. Sort your data on Column B before whatever else you want to sort on.
5. In row 2 of another new column (column H) put the formula =IF(B2=B1,&quot;&quot;,B2)
6. Select Column H down as far as your data in column B goes and do Edit > Copy. Then Select cell B2 and do Edit > Paste Special > Values.
7. Finally delete columns G and H

Enjoy,
Tony

 

[majordog]

Thanks Tony - I had gotten as far as having to fill in col B with values before performing the sort. And then adding blank lines after the sort between each - Not in the manner you suggested above - So I am going to test method and see if it is faster. I don't do this sort of coding so thought I would ask the people who knew what they were doing. Anyways, hope your 4th was good - Sucks you were at work!

 

[VBAjedi]

If you have a solution you are happy with, then ignore this post. . . but the above seemed a bit clunky. Why not:

1) Sort data range (blank rows and all) ascending.
2) Loop through data rows from BOTTOM to TOP (&quot;For x = LastRow to FirstRow Step -1&quot,
a) Test to see if row blank. Next x if so.
b) If Row x Business <> Row x+1 Business, then insert five blank rows after row x
c) Next x

Dunno, that just seems simpler to me. . .

HTH!

VBAjedi

 

[SkipVought]

Why are we putting blank rows in data. As Tony pointed out, blank rows and not conducive with tables.

How about making each RowHeight something like 60. Then...

Sub ExpandRowHeight()
    For Each r In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
       r.EntireRow.RowHeight = 60
    Next
End Sub

Just another way to skin a cat mebe.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com