Range Sort Not Working?

Jun 27, 2014

I'm highlighting a range and then sorting column F from Largest to Smallest. The macro doesn't sort it at all. Added my code tags.

View 1 Replies


ADVERTISEMENT

Code For SORT Is Not Working

Jan 20, 2010

why my vba code for sort is not working here. When I do F8 it simply passes the sort code (the one in red) with no action happening. Why.? and do I need to do. Thanks.


Sub Macro1()
'
' Macro1 Macro
'
Sheets(1).Copy Before:=Sheets(1)
ActiveSheet.Rows("1:2").EntireRow.Delete shift:=xlUp
ActiveSheet.Columns("C:M").EntireColumn.Delete shift:=xlToLeft

With ActiveSheet
With Range("A1:C10000")
.Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlGuess,_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End With
End With

View 9 Replies View Related

Sort Ascending Order Not Working

Sep 25, 2012

In the below code, when I want to sort on Column "A" it is not working. Anything stand out that would prevent my code from working?

Sub Delete_Duplicates()Dim LastRow As LongWith Sheets("SAP Raw Extract")    
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row    'Create ID Column        
.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove        .Range("A1") = "ID"       
 .Range("A2").FormulaR1C1 = "=RC[5]&RC[6]&RC[32]"        

[Code] .....

View 3 Replies View Related

Auto Sort Not Working Correctly

May 12, 2006

I'm try to get the pos. placing in a auto sorted table to remain in place
i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!

View 2 Replies View Related

Excel 2013 :: Sort Data Not Working?

Aug 14, 2013

Ok just started using Excel 2013.

Trying to sort 3 columns:

Column 1:
A1/B1
A2/B2
A3/B3

Column 2:
Team 1
Team 2
Team 3

Column 3:
1
2
3

Column 3 is just a ranking from 1-30. So I select all the data in columns 1 and 2 and hit sort from largest to smallest and it looks like excel computes, but nothing changes and it doesn't sort from largest to smallest? A

View 6 Replies View Related

Sort Numbers Stored As Text Not Working?

Sep 17, 2013

In a spreadsheet I have a text column with entries such as:
2012
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry

When I sort by this column I expect to get:
10 Times
10.5 Times
101 Times
2012
25 Times
A Pure Text Entry

Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.

View 3 Replies View Related

Excel 2010 :: Filter - Sort Not Working?

Oct 24, 2011

I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7

View 5 Replies View Related

AutoFilter And Sort Not Working On Protected Sheet

Dec 20, 2012

When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:

Code:

With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With

I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.

View 1 Replies View Related

First Key Sort Order Not Working Properly In Pivot Table

Jul 23, 2012

I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.

I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.

View 1 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View 3 Replies View Related

Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009

I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer

For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function

View 9 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View 9 Replies View Related

Range Is Not Working

Apr 13, 2009

i dont know what's wrong with these lines:

View 3 Replies View Related

VBA Sorting Nth Range Not Working

May 16, 2009

I have a values in column C of stuff1, stuff2 and on down.

I have a value in cell A1.

I want to start in C1, and every Nth, where N is the value in cell A1, select and paste into column H.

My problem is that the selected cells don't start in C1, in fact, based on the value in A1, it starts in different cells in column C. AND, if i press the button twice, the returned values are different.

View 14 Replies View Related

Range Select Not Working?

Feb 17, 2012

The code works up the If statement and I'm not sure why.

Code:
Private Sub SalesRedeemed_Click()
' Open SalesRedeemed Worksheet move to next month
Sheets("SalesRed").Select
Sheets("SalesRed").Range("B5").Select
If Range("B5") > 1 Then
Sheets("SalesRed").Range("B6").Select
End If
End Sub

View 9 Replies View Related

Working With A Variable Range

Feb 14, 2009

My MACRO is used daily to copy data from several sheets resulting in a new sheet with a differing number of rows with every run. My problem is that I need to define this range of rows in order to do an AUTOFILL function.

I know I can use a cell to hold the row count using COUNTA. However, I cannot figure out how to then use the result of this in the range statement.
example using COUNTA within cell D100 produces a row count of 95

My MACRO needs to do the following:
A1 =Month(C1)
B1 =Year(C1)
Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A2:B????"),

View 9 Replies View Related

Indirect Using Dynamic Range Not Working

Dec 27, 2012

In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.

On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.

Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.

I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?

Dynamic Range.xlsx

View 8 Replies View Related

Date Range Formula Not Working

Apr 14, 2014

I am trying to create a formula that if the date column falls between 01/01/2014-01/25/2014, it returns January as the value.

View 10 Replies View Related

Date Range Formula Not Working?

Apr 14, 2014

see the attached spreadsheet, I am getting an error message with the attached formula

View 2 Replies View Related

Named Range Not Working In Some Functions

Apr 29, 2012

I have a problem using a named range as an arg to worksheet functions.

First, I create 2 end point names for the range, myStart, myEnd.
Then I create a named range myRange = myStart:myEnd

This formula in a cell works: SUM(myRange)
This formula doesn't: COUNT(myRange) . It crashes Excel, requests Restart.
But these work: COUNT(myStart:myEnd) and SUM(myStart:myEnd)

Why does SUM work for either set of args, but COUNT only works for one set?

Here is the F3, PasteList output:

mFirstCol =mFirstColMinus1+1
mFirstColMinus1 =COLUMN(Sheet1!$A:$A)
mInsertCol =COLUMN(Sheet1!$B:$B)
mLastCol =MAX(mFirstCol, mInsertCol-1)
mNumberCols =mInsertCol-mFirstCol
mRowEnd =INDIRECT(ADDRESS(1,mLastCol))
mRowRange =mRowStart:mRowEnd
mRowStart =INDIRECT(ADDRESS(1,mFirstCol))

View 4 Replies View Related

Range Content Removal Not Working

Aug 24, 2012

what is wrong witht his code?

If Sheets("Workings").Cells(ExceptionCount, ColumnCount + 3).Value = 1 Then
Sheets("Allocations").Range(.Cells(ABIndex + 5 + week, (ColumnCount * 7) - 5), .Cells(ABIndex + 5 + week, ColumnCount * 7)).Value = ""
Else
End If

I know the If statement is working, but for some reason I can't fathom the line that removes the cell contents does nothing. I have tried ending with .ClearContents rather than .Value but that doesn't work either.

View 7 Replies View Related

2007 / Range(Cells(n,n)) Not Working

Sep 13, 2008

I've worked with Excel for many years and have just begun writing VBA in Excel 2007, so I am really stumped by this:

SR = 1
SC = 1
TR = 40
TC = 2

Worksheets("ChartData").Activate

' Works
ActiveSheet.Range("A1:B40").Select

' All of the following Fail w/1004 Error
ActiveSheet.Range(Cells(1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(40, 2)).Select
ActiveSheet.Range(Cells(SR, SC), Cells(TR, TC)).Select

At first I thought I had a bad install,
but this is happening on two manufacturer installed systems...
And I seem to be following the documentation for the use of Range with Cells.
Am I missing something?

View 9 Replies View Related

Working With A Subset Of A Named Range

Dec 19, 2008

I have a sheet of data that is imported from an external database. Depending on which report gets imported, the column count and column location will vary. However, the headers for the columns I need to work with are constant, therefore I create a named range for all columns, and reference the ranges as I work with the data and avoid hard-coded cell references. Therefore, the ranges are one cell wide, with up to 2000+ rows.

Here's my problem. I need to reference a sub-set of a named range, to get the min of the subset. I've searched this board and others, but can't find an example of this method. The code is within a loop, and the range subset location is determined by a count on another named range.

The data involves Tasks and Subtasks. Each task can have multiple subtasks, and each subtask has it's own start and end dates. I'm trying to determine the earliest start date and the latest end date for each individual Task so I can determine the total duration for the Task.

Here's the code that determines the cell range. This code is working.

d2 = WorksheetFunction.CountIf(Range("ASR"), Range("ASR").Cells(y, 1).Value) ' count number of tasks in this ASR, store in d2

The code that I can't figure out is below. The compiler can't figure out what I'm trying to do. (Neither can I!!)

d3 = WorksheetFunction.Min("Planned_Start_Date").Cells(y, 1), Cells(y + d2 - 1, 1).Value) ' Get the min Planned Start Date for this ASR

In english: Look in the Planned_Start_Date named range, and more specifically a certain subset of that range, and return the minimum date found.

I've tried several ways, none of which will compile. I realize the above code is way off; I include it to show how lost I really am!

Slightly off-topic: I'm fairly new to VBA, and am expected to create executive dashboards with the data. I've purchased Excel 2003 Power Programming with VBA and Excel Charts (both Walkenbach); Excel Programming 2nd Edition; Excel Edition of Using Visual Basic for Applications; and Dashboard Reporting with Excel, Kyd. These are all useful books, but I think they expect me to have a better understanding than I have.

View 9 Replies View Related

Working With Recordset In A Range Of Cells

Oct 14, 2006

how i can display different values using the Selection.FormulaArray, ok look at my
Dim mySheet As Worksheet

' On Error GoTo errors
Dim x As Integer
x = TextBox1.Text
Dim y As Integer
y = RefEdit2.value

Set mySheet = Excel.ActiveSheet
mySheet. Range(RefEdit1).Select

Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)"
MsgBox Selection.FormulaArray

Now the function i call:

Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer
multiplie_value = Val(valor) * multiplo
End Function

thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.

View 9 Replies View Related

Sort Range

Mar 6, 2009

I would like it to sort for me and I can't seem to get that going. The sort order should be by C ascending, then by B ascending and by d ascending. I am adding this to a macro that is already running. The beginning of the macro copies the sheet and renames it based on myinput.

HTML Dim LstRow As Long
Dim Rng As Range
LstRow = Cells(Rows.Count, "A").End(xlUp).Row - 1
Set Rng = Range("A2:m" & LstRow)
Rng.Select

View 3 Replies View Related

Range Name And Sort

Jan 18, 2007

i have a large sheet that was set up starting at cell A3

A3:P19 is one week of data
A20:P38 is the next week of data

Each week contains 17 rows.

What i am trying to get is something that will Name each week, starting with the first week of data A3:P19 "1"
the next 17 rows, a to p is named 2
the next 17 rows, named 3

etc etc.

Then i want to sort by range, in decending order. ( I hope this is possible)
is this doable? it will save alot of time if it is.

View 9 Replies View Related

Compare Formula (For Vertical Range) Not Working

Feb 21, 2014

I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3. I have tried the following but it did not work.

Sub Searching()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)

[Code] ......

View 1 Replies View Related

Cells Range Reference Not Working Properly

May 25, 2009

I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.

Dim cnt1 as integer, cnt2 as integer,cnt3 as integer

cnt1 = 2
cnt2 = 50
cnt3 = 2

dim myrange as range

set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))

I have no idea why it won't work. I'm basically searching a columner range of cells.

View 2 Replies View Related

Sumifs With Month (range) Criteria Not Working

Aug 14, 2013

I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.

I have never used the SUMIFS before and having trouble, here is the equation I'm using.

=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")

*F2:F204238 is the range that needs to be summed up
*the month(referenced range) is in date form
*I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.

View 3 Replies View Related

Range Array Sort

Oct 20, 2009

I am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges
CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved