Code not working get error

[juan]

Hello,
I get the following error Run-time error Method "UsedRange
of Object_Worksheet' failed. Before it has been working
but not sure if I should reinstall Excel.
This is the code:
Dim Firstrow As Long
Dim lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationAutomatic
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow -
1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there
is a error in the cell

ElseIf .Cell(Lrow, "A").Value= "Semi Total" Then
.Cells(Lrow, "A").Value = ""
.Cells(Lrow, "A").Offset(0, 1).Value = "Semi
Total"
ElseIf .Cells(Lrow, "A").Value = "SIL Total" Then
.Cells(Lrow, "A").Value = ""
.Cells(Lrow, "A").Offset(0, 1).Value = "SIL Total"
End If
Next
End With
End Sub
When I do the debug it highlights
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
So not sure what to do. Any suggestionw would be
appreciated.
Thanks,
juan

 

[Simon Murphy]

Juan
I dont think you need re-install
Is this worksheet empty?
You could try activating the active sheet or selecting a
cell. Or use something other than used range (eg cells
(1,1) to lastcell.

You could also wrap this in an error handler.

I've had this sort of error before and it usually settles
down without anything as major as a re-install. closing
and reopening Excel might also help, or a re-boot.

cheers
Simon

 

[juan]

Hello Simon,
No the worksheet is not empty. Basically, I have a huge
macro. Before I run the below code, The macro Does A
subtotal and what the code below does is Moves the text
from Column A to Column B. Example,
Column A Column B
SIL Total Moves to here Sil Total
So after I move I delete Column A. What you mean
Activating the active Sheet? Is it possible for you to
provide sample of maybe how the code should be? It was
working fine until yesterday. Basically, I want to move
from column A any text with the word Total and move to
Column B and then delete Column A. Do you have a
alternative way to do this?
Thank you for your help and would appreciate if you have
any more suggestions.

Thanks,
Juan

 

[Juan]

Hello Simon,
I made some changes to another portion of the macro and
seems that its working now. So no need for you to spend
time on this. So thank you for the info you provided.

Thanks alot,
Juan