View Single Post
  #1 (permalink)  
Old February 15th, 2012, 07:20 PM
J.Suzuki J.Suzuki is offline
Registered User
Points: 14, Level: 1
Points: 14, Level: 1 Points: 14, Level: 1 Points: 14, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Code for dynamic Range value in recorded macro

Hi all,

I recorded a macro that sorts a spreadsheet by 4 columns. Below is the VBA that was recorded. How do I replace the fixed range (for example: Range("B2:B867") ) with the row count instead of 867? Thank you!

Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("F2:F867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("L2:L867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:N867")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Reply With Quote