PDA

View Full Version : [SOLVED] Fault in Code, Do I Error code it or write code to account for it?



LutonBarry
06-05-2013, 05:04 AM
Hello folks I'm back again for more help. I have written some code that selects cells that are beneath the title bar for copying. This is on a spredsheet that details and calls we have closed the previous day. The code fails if there are no calls and in effect the selection is zero lines. What would you suggest as I'm a bit of a novice really.

It fails at this point in the code it is in BOLD and underlined below but if not here it is.
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select



Set myRange = Range("A1").CurrentRegion
myRange.Select
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$4106").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$S$4106").AutoFilter Field:=1, Criteria1:="Today"
ActiveCell.CurrentRegion.Select
Set myRange = Range("A1").CurrentRegion
myRange.Select
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$4462").AutoFilter Field:=1
Cells.Select
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

p45cal
06-05-2013, 07:27 AM
That's because myRange.Rows.Count is 1 (the header row only). When you then subtract 1 from it you're left with 0 and a range with 0 rows doesn't make a lot of sense. so you need to check for this with say:
If myRange.Rows.Count > 1 then ..carry on doing stuff .. else ..stop doing stuff (I guess)

I infer that the sheet already has an Autofilter on it when you start the code and you want to delete what's visible, you then clear the filter from column 2 and then filter for 'Today' in column 1, and delete that too, then remove that filter and what's left is what you want to do further work on?

Is that right?

SamT
06-05-2013, 07:34 AM
OP code cleaned:

Set myRange = Range("A1").CurrentRegion
myRange.Select
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$4106").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$S$4106").AutoFilter Field:=1, Criteria1:="Today"
ActiveCell.CurrentRegion.Select
Set myRange = Range("A1").CurrentRegion
myRange.Select
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$4462").AutoFilter Field:=1
Cells.Select
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

SamT
06-05-2013, 07:58 AM
Improved OP code with added Error handler:

With ActiveSheet

'Delete Visible Rows
Set myRange = Range("A1").CurrentRegion
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Delete Shift:=xlUp

'Filter on "Today"
.Range("$A$1:$S$4106").AutoFilter Field:=2
.Range("$A$1:$S$4106").AutoFilter Field:=1, Criteria1:="Today"

'Delete Visible Rows ("Today")
Set myRange = Range("A1").CurrentRegion
'Handle No Rows = "Today"
If myRange.Rows.Count > 1 Then _
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Delete Shift:=xlUp

'Show remaining Rows
.Range("$A$1:$S$4462").AutoFilter Field:=1
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

End With

LutonBarry
06-05-2013, 09:51 AM
That's because myRange.Rows.Count is 1 (the header row only). When you then subtract 1 from it you're left with 0 and a range with 0 rows doesn't make a lot of sense. so you need to check for this with say:
If myRange.Rows.Count > 1 then ..carry on doing stuff .. else ..stop doing stuff (I guess)

I infer that the sheet already has an Autofilter on it when you start the code and you want to delete what's visible, you then clear the filter from column 2 and then filter for 'Today' in column 1, and delete that too, then remove that filter and what's left is what you want to do further work on?

Is that right?

P45Cal yes you've got that right that's exactly what I am doing and ending up usually with a list of calls after filtering out those I no longer need. I'll try this one thanks so much.

LutonBarry
06-05-2013, 09:56 AM
Improved OP code with added Error handler:

With ActiveSheet

'Delete Visible Rows
Set myRange = Range("A1").CurrentRegion
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Delete Shift:=xlUp

'Filter on "Today"
.Range("$A$1:$S$4106").AutoFilter Field:=2
.Range("$A$1:$S$4106").AutoFilter Field:=1, Criteria1:="Today"

'Delete Visible Rows ("Today")
Set myRange = Range("A1").CurrentRegion
'Handle No Rows = "Today"
If myRange.Rows.Count > 1 Then _
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Delete Shift:=xlUp

'Show remaining Rows
.Range("$A$1:$S$4462").AutoFilter Field:=1
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

End With



Sam thanks so much for this trimmed down version of my code. I record most of it and have not got my head around what I can do without yet.

Quick question if you do not mind on filtering code, for example that below. Can the range be specified as
Range("A:A").Autofilter Field:=2

For example and avoid the reference to cells?

'Filter on "Today"
.Range("$A$1:$S$4106").AutoFilter Field:=2
.Range("$A$1:$S$4106").AutoFilter Field:=1, Criteria1:="Today"

p45cal
06-05-2013, 10:44 AM
You can use the likes of:
.AutoFilter.Range.AutoFilter Field:=2
.AutoFilter.Range.AutoFilter Field:=1, Criteria1:="Today"
(though I fancy there'd be a more elegant way)

SamT
06-05-2013, 02:50 PM
Sam thanks so much for this trimmed down version of my code. I record most of it and have not got my head around what I can do without yet.

Two Selectings in a row?


myRange.Select
myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select or

myRange.Offset(1).Resize(myRange.Rows.Count - 1).Select
myRange.Select Delete the first line

Select something, then do something to the Selection?

Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Delete the "Select - Linefeed - Selection"
[vCode]Range(Selection, Selection.End(xlDown))Delete Shift:=xlUp [/Code]


Oooh! Bad, bad, bad. Do you really, truly, and always KNOW what cell is Active?
ActiveCell.CurrentRegion.Select
Specify the Cell to use.
Range("A1").CurrentRegion.Select

LutonBarry
06-05-2013, 03:59 PM
Sam,

Thanks once again for that advice. When spelled out like that it is so obvious. Even the [Code] ActiveCell.CurrentRegion.Select [\Code] section highlights an area where recording code can possibly contribute to future issues. Am I going to be doing some editing tomorrow.

Many thanks as always.

LutonBarry
06-06-2013, 06:16 AM
P45Cal and Sam I cannot thank you both enough for your time not only are you solving my issues I am learning so much in th eprocess to and it is much appreciated. Your suggestions worked and all I had to do was to insert an End With statement in order for it to complete.

So thanks once again lads.

Barry:beerchug: