Sort column

  • Thread starter Thread starter pierreeng
  • Start date Start date
P

pierreeng

Hi dudes, i m wondering how the follow situations is solved using VBA.

Say,

A
A1 3lke
A2 het, her
A3 3kle
A4 8
A5 90

And i wanna get the result as follows:

A
A1 3lke
A3 3lke

A2 het,her

A4 8

A5 90

Meaning i wanna sort the rows according to the 2nd column, the sam
data are sorted together then leave a blank b4 a different data. Tak
note that alphabetical sequence is not important in this case.

To provide more info, there will be unknwon rows and i need to do thi
on several sheets.

Can anyone gimme some guidelines?

Thks and have a nice day ahead
 
oops. forgot to ask this in my previous post.

Is there any command similar to followings

Worksheets(5).Rows(8).move before:= worksheets(5).Rows(3)

* The above code is invalid.*

I know this kind of code works on shifting worksheets. So for shifting
rows, is there any similar code? i know i can use offset, but i m not
really familiar with that.

Anyone can help? thks.
 
Maybe something like:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3"))
With wks
Set dummyRng = .UsedRange
.Range("a1", .UsedRange).Sort _
key1:=.Range("b1"), order1:=xlAscending
FirstRow = 3 'header in 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing
Else
.Rows(iRow).Insert
'''' .Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
End If
Next iRow
End With
Next wks

End Sub

The commented line that doubles the rowheight might be a better way to go.
Instead of inserting a new row (and possibly screwing up
charts/pivottables/autofilters/...), maybe just giving the appearance of a
double space would be enough.
 
Back
Top