[VBA] Sort values in column with respect to blank rows

Ideal Being

New Member
Joined
Mar 28, 2015
Messages
16
Hi,

I have a table which can look like this (data are simplified) after some macros are activated:

Column 1
Column 2
Column 3
Column 4
1
A
cat
3
C
dog
D
bird

<tbody>
</tbody>

I have a userform with listbox where are values A-Z and following logic:
  1. Select multiple items from listbox
  2. Click add button to add them into active sheet
  3. Delete duplicity (if you add let's say A and then you want to add it again, it does NOT add it - this can guarantee unique values)
  4. Sort whole table by column 2 in alphabetical order
  5. Add 2 rows for each row which has value in column 2

The problem is when the macro is activated second time (let's say I want to add value B to column 2), the sort mechanism obviously does not work as it sorts blank cells:

Code:
'Sorts test in ascending order by Column C in Range B5:J1000
Columns("C:C").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range _
    ("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("B5:J1000")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Is there a way to tell the sort mechanism that it needs to expand selection and take all 3 rows related to value in column 2? So the final table after adding B into column 2 would look like this:

Column 1
Column 2
Column 3
Column 4
1
A
cat
2
B
3
C
dog
D
bird

<tbody>
</tbody>
All suggestions are appreciated. Thanks to everyone for your time and patience!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Ideal Being

New Member
Joined
Mar 28, 2015
Messages
16
Is there a chance there is better way than following solution?

Before sorting:
  1. When there is value in column 2, copy it into next 2 rows
  2. Sort table based on column 2
  3. Delete values added in step 1 (however I am not sure how to implement this)

My first though was to merge cells in column 2 (cell with value with 2 following rows) and use it to sort but it is not possible to sort table when cells are not the same size..


Any suggestions appreciated, any advice or piece of VBA can save me lots of time. Thanks in advance to everybody investing time into my problem!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

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