?
Solved

Identify a section of an Excel sheet and then sort it.

Posted on 2013-11-09
3
Medium Priority
?
202 Views
Last Modified: 2014-01-09
In my Excel data sheet, I already have chosen a row number as the start of a section of data rows, how can VBA determine the last row of that section (this is the row that has no data/blank in column A). So now, I have identified the start and stop row numbers.

Then if I have a column number (another integer) I would like the VBA to sort the section of rows in descending order. (at that point I will sum the first n entries to get a concentration value of some type(ie ;the top 10 items encompass 22% of the total value...')
 
This will help me learn how to identify variable sized data blocks and then sort/manipulate them.

Thanks.

In my sample file, the data starts on row 2 and I would sort on column 3, descending.
0
Comment
Question by:donohara1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39635879
In my sample file, the data starts on row 2 and I would sort on column 3, descending.

No sample file attached.

Have you tried using named ranges?
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39635889
Alternatively, you can use a table:

A1 is Header - DATA
A2:A36 - data values

Turn A1 to A36 into a table, where DATA is the Label.

From VBA, you can reference this as Range("DATA")

To sort these descending, use:

Sub Sort()
    mySort = Range("data1").Sort(Key1:=Range("a1"), order1:=xlDescending)
End Sub

Open in new window


Now that you have a table, any additional data values entered are now added to the same Label (i.e. your code does not have to change).
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 39638663
Will all of column A be populated until you reach the bottom of the data range? In other words, no blank cells part way down.

If so you can identify the last row in a number of ways eg

Range("A1").Select
Selection.End(xlDown).Select
LastRow=ActiveCell.Row

Open in new window

There are other options that don't involve selecting.

Thanks
Rob H
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question