Resizing my range - End row
I have used the following syntax to determine the range for a For Each
statement. However I'd like the end row to be 1 less than is returned so I enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA. How do I fix? Many thanks Jacqui Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1) |
Resizing my range - End row
Hi Jaqui,
Try: Set myRng = .Range("c20", _ .Cells(.Rows.Count, "c").End(xlUp)) Set myRng = myRng.Resize(myRng.Count - 1) --- Regards, Norman "Jacqui" wrote in message ... I have used the following syntax to determine the range for a For Each statement. However I'd like the end row to be 1 less than is returned so I enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA. How do I fix? Many thanks Jacqui Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1) |
Resizing my range - End row
How about:
Set myRng = .Range("c20:C" & .Cells(.Rows.Count, "c").End(xlUp).row - 1) well, as long as there was something in column C after row 1. Jacqui wrote: I have used the following syntax to determine the range for a For Each statement. However I'd like the end row to be 1 less than is returned so I enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA. How do I fix? Many thanks Jacqui Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1) -- Dave Peterson |
Resizing my range - End row
Try
Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp).row - 1) HTH "Jacqui" wrote: I have used the following syntax to determine the range for a For Each statement. However I'd like the end row to be 1 less than is returned so I enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA. How do I fix? Many thanks Jacqui Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1) |
All times are GMT +1. The time now is 03:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com