Forum Discussion

Antje_CO's avatar
Antje_CO
Copper Contributor
Dec 30, 2019

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

Resources