Range and Sort Selection Issues


New Member
Aug 5, 2010
Okay, so the task I am trying to develop a macro for involves importing a Text file, deleting colums E:J, then sorting the left over data, according to Event Type, then I am supposed to delete out all of the Pulse Events, then past a coded template from a different spreadsheet. That is not the task in its entirety, but is relevent for what I am having trouble with: Sorting the data. The big problem is that each text file I import has a variable amount of enteries, ranging from 220:280, and the sort command requires a set range. Is there any way to set a variable range, or to set the range to what is currently selected? If anyone has any Idea of how I could go about resolving this issue or any questions about what I trying to do, feel free to ask/post. I have Bolded the areas of code I am having trouble with

what I have gotten to so far is this:

ActiveCell.Offset(0, 4).Columns("A:F").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(5, 0).Activate
Selection.Delete Shift:=xlUp
ActiveWorkbook.Worksheets("##########").Sort.SortFields. _
ActiveWorkbook.Worksheets("###########").Sort.SortFields. _
Add Key:=ActiveCell.Offset(0, 1).Range("A1:A224"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("#############").Sort
.SetRange ActiveCell.Range("A1:E224")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...