Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi
I have attached a macro to a button to allow me to easily sort my data. The ascending sort works fine, but the descending sort lists all the blank cells first, when I want them to appear last. The blanks result from the formula =IF(ISNUMBER('All Share'!T612),('All Share'!T612),"") and I need to keep the formula in place. Can you help?
How about adding some extra code to your macro to move the blank cells to the bottom of the list after the sort?
Use a loop to build the range of blank cells then do a cut/paste.
Or sort ascending then sort descending on just the cells with data.
0 
Would you be able to suggest how to do this please?
My attempt below did not work.
Sub Dividend_Yield_Descending()
'
Range("8:8", Range("B8").End(xlDown)).Sort Key1:=Range("G8"), Order1:=xlDescending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Range("G8").Select
Call Find_non_blank
End SubSub Find_non_blank()
Curr_row = Selection.Row
Curr_col = Selection.Column
test = 0
Do While test = 0
If IsNumeric(Cells(Curr_row, Curr_col).Value) = True And Cells(Curr_row, Curr_col).Rows.Hidden = False Then
test = 1
Else: Curr_row = Curr_row + 1
End If
Loop
Cells(Curr_row, Curr_col).Select
End Sub
0
Try this...
You know your spreadsheet better than I do, so you might have to play with the ranges a little, but I think the concept works. As always, you should test this on a backup copy of your spreadsheet.
Sub Dividend_Yield_Descending() ' Range("8:8", Range("B8").End(xlDown)).Sort Key1:=Range("G8"), Order1:=xlDescending, Header:= _ xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom Call MoveBlanks End Sub Sub MoveBlanks() ' 'Count rows that were sorted RowCount = Range("8:8", Range("B8").End(xlDown)).Rows.Count 'Loop through Column G range counting blanks For Each cell In Range("G8:G" & 8 + RowCount - 1) If cell <> "" Then GoTo countDone myBlanks = myBlanks + 1 Next countDone: 'If there are any blanks, Cut them and Insert above bottom of range If myBlanks > 0 Then Range(Cells(8, "G"), Cells(8 + myBlanks - 1, "G")).Cut Range("G" & 8 + RowCount).Insert End If End Sub
0
Thanks for this.
I've tried to change your suggestion so that the cut doesn't just move the blank cells in the G column, but also the corresponding cells in the other columns in the range (B:AG), making them move together as they do during the sort. However changing the range from G to B:AG doesn't work.
I'd really appreciate it if you can point me in the right direction again.
0
To move entire rows try this:
If myBlanks > 0 Then Range(Cells(8, "G"), Cells(8 + myBlanks - 1, "G")).EntireRow.Cut Rows(8 + RowCount).InsertTo move just the range B8:AGxx try this:
If myBlanks > 0 Then Range("B8:AG" & 8 + myBlanks - 1).Cut Range("B" & 8 + RowCount).Insert End If
0
Is there a way that I can select the range without using the row and column numbers, rather than (B8:AG626)?
This is becuase I'm using another macro to replace the data set that the first macro is used on. The second data set is smaller, and despite the use of your suggested macro, sorting the cells by ascending order results in blank cells appearing at the top of the new list (the same number by which the first data set is bigger than the second).
0
I'm confused by your question.
There are 3 common methods for working with a range in VBA:
1 - Using hardcoded strings e.g. Range ("B1:AG5")
2 - Calculating the range e.g.
Range ("B1:AG" & row_num) Range(Cells(row_num1, col_num1), Cells(row_num2, col_num2)) Range(Cells(row_num1, "B"), Cells(row_num2, "AG"))There are a number of different syntaxes that can be used for this, but the point is that you can build the range by using variables or values from a cell in a worksheet, etc.
3 - Using Named ranges, which can be static or dynamic.
Therefore when you ask Is there a way that I can select the range without using the row and column numbers, rather than (B8:AG626)? I'm not sure how to answer.
You say Is there a way that I can select the range without using the row and column numbers which tells me you don't want to use row and column numbers, but you also say rather than (B8:AG626) which also implies you don't want to hardcode the ranges. That's 2 negatives, isn't it?
0
Thanks for all your help
I was a bit confused earlier, and now think that I can solve my problem in an easier way.
I need to insert the cut cells at the top of the list rather than paste them at the top, as the blank cells have not been shifting down and I therefore lose the formula in them.
I'm trying to use the macro below to insert the data rather than paste it, but simply replacing paste with insert does not work. Can you suggest a way to get round this?
If Range("C4") = "'BDP - SXXP'" Then
Range("B27:AG626").Select
Selection.Cut
Range("B8").Select
ActiveSheet.Paste
0
Rarely do you need to Select a range to perform an operation on it. You'll notice that I never selected anything in any code I've suggested in this thread.
Not only does it slow the code down, it sometimes causes problems with the execuion of the code.
Try this instead:
If Range("C4") = "'BDP - SXXP'" Then Range("B27:AG626").Cut Range("B8").Insert End If
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
