Solved: Sort will not work ...

[vodkasoda]

Sorry, but I can't figure out what I've done wrong here, surely it's something very simple !!!

Basically, I am trying to take Data from A1 to the last D cell before a blank Row, and copy it to the Row after the blank one, but in Column D ascending order ...


Option Explicit

Sub ReplaceData()

'Application.ScreenUpdating = False

Dim MyRow As Long

MyRow = (Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Row)
Range("A" & MyRow & ":D999").Delete

Range("A1:D" & MyRow - 1).Copy Destination:=Range("A" & MyRow + 1).Sort(Key1:=Range("D2:D" & MyRow - 1), order1:=xlAscending, Header:=xlNo)

'Application.ScreenUpdating = True

End Sub

[CatDaddy]

are you trying to delete the blank rows as well?

[vodkasoda]

The idea is that whenever data in the Top Range (A1 to Dxx) is amended, all data below that Range is erased and replaced with the newly updated data, but sorted into the Column D values.

I know how to do everything except the sort ...

[frank_m]

Don't think you can combine the copy destination with the sort in that way.
If the last row of data is always 999, this might work, or get you closer.
Sub ReplaceData()

'Application.ScreenUpdating = False

Dim MyRow As Long

MyRow = (Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Row)
Range("A" & MyRow & ":D999").Delete

Range("A1:D" & MyRow - 1).Copy Destination:=Range("A" & MyRow + 1)

Range("A" & MyRow & ":D999").Sort Key1:=Range("D2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal
'

'Application.ScreenUpdating = True

End Sub

[vodkasoda]

Thanks Frank, had to tweak it a little bit to get the header to stay in place, but it works just fine now ... :friends:

Dim MyRow As Long

MyRow = (Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Row)
Range("A" & MyRow & ":D999").Delete

Range("A1:D" & MyRow - 1).Copy Destination:=Range("A" & MyRow + 1)

Range("A" & MyRow + 1 & ":D" & (MyRow * 2) - 1).Sort Key1:=Range("D" & MyRow + 1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

[vodkasoda]

How do I mark this as Solved ?

[Paul_Hossler]

How do I mark this as Solved ?


"Thread Tools" at the top

Paul

[vodkasoda]

I tried this before, I can't see anything that says Close Thread or Mark Thread As Solved ... I just see Show Printable Version, Email this Page, Unsubscribe from this Thread, Search this Thread, Display Modes & Rate This Thread ... am I going mad :aw ?!?!?

[frank_m]

Sign in > select Post#1 in your thread..
near the top far right, in the 2nd of 3 green colored bands that run across the page, you should see Thread Tools, Search this Thread, Rate Thread, Display Modes, Post#1 - Click the Thread Tools dropdown list, put a tic mark next to solved, then click apply action.

[vodkasoda]

Sign in > select Post#1 in your thread..
near the top far right, in the 2nd of 3 green colored bands that run across the page, you should see Thread Tools, Search this Thread, Rate Thread, Display Modes, Post#1 - Click the Thread Tools dropdown list, put a tic mark next to solved, then click apply action.


Aha ... well this is the problem, in CHROME, the Thread Tools is just a shortcut, it's not a Dropdown, so the Solved option is never available !!!

I've set it to Solved by using IE, but it's not something I want to do again, it has to be the worst software on the market, it cripples my PC completely, just by running it, I don't even have to *use* it !!!