Sorting In Excel

[SandraF]

Here is the problem.

I want to sort rows of information by a column using a macro.
The only problem is that there is no set of rows. One time there sould be 4 rows, another day there could be 45

I have the following code so far:
rows.Worksheets("DB&quot.Range("?????????&quot.Sort _
key1:=Worksheets("DB&quot.Range("????&quot
Set currentCell = Worksheets("DB&quot.Range("????&quot
I want to sort by Column L then by Column R


I am thinking that before I sort, I should count how many rows there are in the worksheet. Then I could do something with that information.

Any info would be appreciated!!

 

[TulsaJeff]

Use Range.currentRegion()

A region is a block of cell bounded by blank rows and columns.

I will have to try it and send you the code but meanwhile you can play with it and maybe make it work for you.

I have used it before in similar situation where my area was contantly changing. It works like a champ Ya' Gotta Love It!

 

[Gypdude]

This could tell you how many rows there are:

t1$ = Cells(1, 1).Value
row = 1
If t1$ <> &quot;&quot; Then
row = 2
Do
t1$ = Cells(row, 1).Value
If t1$ = &quot;&quot; Then
maxrow = row - 1
Exit Do
Else
row = row + 1
End If
Loop
End If

 

[dsi]

You don't have to select the specific rows. Just select the entire columns and do the same sort. All empty rows will automatically be ignored.

Columns("A:L").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,_
               Header:=xlGuess, OrderCustom:=1, _
               MatchCase:=False, Orientation:=xlTopToBottom

 

[kruskovac]

This little sub will tell you how many rows you have with something in them:

Sub GetRows()
Dim C As Range
Worksheets(1).Activate
[F10:F10010].Select
For Each C In Selection.Cells
If C.Value <> &quot;&quot; Then
Counter = Counter + 1
End If
Next C
End Sub

You can change the range to be whatever you need to in your case. I usually just give it something huge, to cover any amount of rows that the user could possibly put in; in this case I accounted for 10000 rows. This is also a nice subroutine because it gives the amount of rows back in the variable &quot;Counter&quot;, which I have as a Global variable. Then I can use that count later in analysis.

 

[DarkSun]

kruskovac, I find it easier to just use:

Worksheetfunction.CountA(Activesheet.Range(&quot;F:F&quot)