• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find move a row up to order

[pre]
Code: 
TimeStamp = Cells(ActiveCell.Row, "D").Value

Stove the Time stamp from column D in the same row as the cell that is currently selected. So, if you select cell C3 and then run the macro, it will store the value from D3 into TimeStamp.

[pre][code]Set DTable = Selection.CurrentRegion

Current Region is the "block" of cells that the object (in this case, the current selection) is in. Knowing that your data is in a table, this defines DTable as the entire data table. We want to know this so we can apply the AutoFilter to the whole table.

[pre][code]'Delete this line if AutoFilter is already active
DTable.AutoFilter
[/pre]

Toggles the AutoFilter on/off. Hence why you would want to remove this line if you are already using AutoFilters


'Filter down to just the relevant info
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:= _
"<=" & TimeStamp[/code][/pre]
Now that AutoFilter is on, applies a filter to the fourth filter (aka, column D). Criteria being everything that is less than or equal to TimeStamp, which we defined earlier as the date/time value from column D.


Set xSelect = DTable.Offset(1, 0).Resize(DTable.Rows.Count - 1, _
DTable.Columns.Count).SpecialCells(xlCellTypeVisible).EntireRow[/code][/pre]
This is the somewhat tricky one. I need to define the range of visible cells that are shown after applying the filter. Starting with DTable, we shift 1 row down so we don't include the header row. Then, we resize the area to be 1 row less than DTable was but the same number of columns. So, if DTable was A1:D5, we're no looking at A2:D5. Then, I used SpecialCells to only look at the visible cells within that range, and finally, I tell it to take the Entire Row of those cells (that were visible).
 
Hi Luke,

back with some problem


if i have another column E & want to select those rows which have less time value in E column than selected row D column value.


ActiveSheet.Range("A1").AutoFilter Field:=5, Criteria1:= _

"<=" & TimeStamp


is it okay or any problem on it.
 
Looks good. You'll also need to change the line at bottom to remove filter

[pre]
Code: 
'Unfilter
ActiveSheet.Range("A1").AutoFilter Field:=5
[/pre]
 
thanks.


In my previous, when there is two similar condition of priority arise first time it move the cell above with obeying all the rules, but if i run macro again then the other similar priority cell move top the order.if i continue run macro then both the cells only alter their places.


So, i want a condition introduce that if cell color of A is not red &


Criteria1:= _

"<=" & TimeStamp


for filtering the cells.
 
If there's no other colors being used, you might be able to do this:

[pre]
Code: 
'Filter down to just the relevant info
ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:= _
"<=" & TimeStamp

ActiveSheet.Range("A1").AutoFilter Field:=1, Operator:= _
xlFilterNoFill
[/pre]
 
hi luke,

Can you please explain this

ActiveSheet.Range("A1").AutoFilter Field:=1, Operator:= _

xlFilterNoFill
 
Hmm. Could you somehow create a helper column that could use the same logic as to why some cells are red, and display True/False for those? Then you could filter that column...
 
You stated that col A has some cells marked red. Is there a logical formula/reason why they are red, or is it random? If not random, we might be able to build a helper column with a formula that uses similar logic, and then we can filter the helper column.
 
ok sir,


i explain it more


after filtering the cell & move up to the order i want that cell not to participate again in filtering condition but in present scenario it participate.


So my idea was that when a filtered cell move up then i marked its col A to any color(take it red) & put a condition so that it will not again participate in filtering again.
 
How about at end of code we have it mark an unused column with an "X", and then when we are filtering, we can filter that same column for <>X?
 
it will be great sir.


did you go through my work book.


there select row 6 & run macro again & again & see the problem.
 
Hi luke,


can you tell me how to filter the column(say F)


mark an unused column with an "X", and then when we are filtering, we can filter that same column for <>X?


as you say it above.
 
You would need to add this line:

[pre]
Code: 
ActiveSheet.Range("A1").AutoFilter Field:=5, Criteria1:= "<>X"
[/pre]
Note that the Field number indicates which column to apply to, and the Criteria is just the rule you want to follow.
 
You can apply up to 2 criteria for each field. As there are no other criteria being applied to column F, you should leave it as Criteria1.
 
Thank you sir for your kindly help & time you give for this thread.

thank you again & really grateful to you.
 
@Luke M,

Hello sir,


i have a problem, when i run macro it is not working.


http://www.2shared.com/file/2X6TsOBe/as2.html
 
@SirJB7


hello sir,


could you find out the problem in my uploaded file.


I think this section does not work


ElseIf WorksheetFunction.CountIf(BRange, "YES") > 1 Then

'multiple YES found in col B


With BRange

Set c = .Find("YES")

firstAddress = c.Address

'Check for corresponding C value

If c.Offset(0, 1) <> "YES" Then

Do

Set c = .FindNext

Loop Until c.Offset(0, 1) = "YES" Or c.Address = firstAddress

End If

End With

Set CutRow = c.EntireRow

Else

'YES only found in col C

Set CutRow = CRange.Find("YES").EntireRow

End If


http://www.2shared.com/file/XLdfRDHA/as2.html
 
Hi, sparcle!


I downloaded your file, clicked on Sort command button, and got errors unless I selected previously a cell from G5:G13 cells (and not B1, or rows 5:13, for example), as it's stores active cell contents in a numeric (double) variable.


Despite of that the code run Ok, it reordered rows 5:13. Am I wrong? If I am, please elaborate a bit more and describe more precisely the issue and the environment (cells selected, what clicked on or what run.


BTW, this is an old post (from three weeks to one month) which leads me to this questions:

a) did it worked 3 weeks ago when you wrote this?

http://chandoo.org/forums/topic/find-move-a-row-up-to-order/page/2#post-30323

b) what did it change from that scenario to the nowadays one?

c) if same workbook, I don't understand; if not, please upload original one (working) so as to compare with now uploaded


Regards!
 
You must log in or register to reply here.
Back
Top