Forum Discussion
VBA error: 1004 method 'range' for object global failed
Hello, I guess it´s a just a little mistake in the code, but I can´t find it. Maybe someone can help me.
I have to sort may different Exports (different meaning the first and last written cell is variable) by the column of the first written cell. (I can´t use the "ActiveSheet.UsedRange" Function because than the first cell can be an empty one too)
I managed to select the range, but if I add the" .sort" I get the error: 1004 method range object _global failed
Here the Code so far:
Sub sort_export()
'find the first cell that is not empty
Dim rng As Range
Dim rnglast As Range
Set rng = ActiveSheet.Range("A1:H50").Find("*")
If rng Is Nothing Then
MsgBox "nothing found"
Exit Sub
End If
'find the last cell
Set rnglast = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell)
'Range(rng, rnglast).select -> correct seletion of Range to be sort
ActiveSheet.Range(rng, rnglast).Sort Key1:=Range(rng.Row, rng.Column), Order1:=xlDescending, Header:=xlYes
End Sub
1 Reply
- Riny_van_EekelenPlatinum Contributor
If you change the piece with "Sort Key1" to:
.Sort Key1:=Cells(rng.Row, rng.Column)
.... it should work.