PDA

View Full Version : Selecting a group of cells using the Range.offset syntax



JZB
04-09-2009, 02:42 AM
I am creating a basic loop where I want to clear 4 cells to the right of the activecell, if it contains "EUR". The part in italics is where i am struggling. any ideas?

thanks

Range("E1").Select
nlastrow = Range("A65536").End(xlUp).Row

For nloop = 1 To nlastrow

If InStr(ActiveCell, "EUR") > 0 Then
Activecell.Offset(0,1):Activecell.Offset(0,4).clear


ActiveCell.Offset(1, 0).Select

Next nloop

JONvdHeyden
04-09-2009, 02:57 AM
Is your currency EUR housed in column E?

JONvdHeyden
04-09-2009, 03:04 AM
Assuming EUR is housed in text strings in column E and you want to clear the next 4 cells adjacent to the right, this may suit. Using autofilter method with tends to be more efficient than a loop when dealing with large ranges.

Sub Clear_EUR()
Dim rCur As Range
Set rCur = Range("E1", Range("E" & Rows.Count).End(xlUp))
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
With rCur
.AutoFilter , field:=1, Criteria1:="*EUR*"
.Offset(, 1).Resize(, 4).SpecialCells(xlCellTypeVisible).Clear
.AutoFilter
End With
Application.EnableEvents = True
End Sub

JZB
04-09-2009, 03:07 AM
correct.

so if E1 was "EUR" then clear F1 to I1. recorded its just Range ("F1:I1").clear.

but for the purposes of the loop, I cannot replicate this using the offset function

cheers

GTO
04-09-2009, 03:07 AM
Greetings,

I noticed that you are looking for the last value in Col A, but wanted to loop thru cells in Col E. As long as this is correct, I believe the following may help:

In a Standard Module:
Option Explicit
Sub ex()

Dim rngECol As Range
Dim rngCell As Range
Dim nlastrow As Long

'// Per the code supplied, you want to check for the last used row in Col A, //
'// allthough you are running through Col E. Long as that is correct... just //
'// wanted to cehck. //
'nlastrow = Range("A65536").End(xlUp).Row
'// Now in case your file is run in 2007, evidently (I have 2000 at home and //
'// 2003 at work, so haven't seen 2007) there are now about 2.3 bajillion rows. //
'// If we use Roes.Count, this will self-adjust for where to start the search //
'// from. //
nlastrow = Cells(Rows.Count, 1).End(xlUp).Row

'// Now we can Set our range rather than depend upon ActiveCell. //
Set rngECol = Range("E1:E" & nlastrow)

'// So... for ea cell in our range... //
For Each rngCell In rngECol
'// we'll check to see if "EUR" is within the cells' values. Note that this//
'// means "EUROPEAN" will also test as TRUE. //
If Not InStr(1, rngCell, "EUR", vbTextCompare) = 0 Then
'// I took it as you didn't want to clear the cell with "*EUR*" in it, //
'// but rather, the four cells to the right of this cell. So we'll //
'// Offset one Col, then Resize to four Columns. //
rngCell.Offset(, 1).Resize(, 4).ClearContents
End If
Next
End Sub


Hope this helps,

Mark

JZB
04-09-2009, 03:14 AM
:friends: thanks. will give this a try

GTO
04-09-2009, 03:15 AM
@Jon:

Hey, hope all is well :-) Slow typist + POL (poor ol' laptop), didn;t know you were answering.

Have a great day,

Mark

JZB
04-09-2009, 03:20 AM
really good, thanks

I am a pretty amateur coder, have a few looping tricks up my sleeve but this code will really help open up a few more ideas

GTO
04-09-2009, 03:31 AM
If that was aimed at me, very happy to help. I am at 'laymen' level (at best) myself, but have sure learned a lot here (everyone can "shush" at how much the blonde guy forgets).

You'll find that most often, ditching Active, Activate, Select and Selection will help speed stuff up and improve reliability.

Anyways, just as importantly, I see this is your first thread. A friendly "Howdy" from Arizona, and a "Welcome!". You'll "meet" some great folks here who certainly go out of their way to help :-)

Have a great day,

Mark

JZB
04-09-2009, 03:41 AM
thanks Mark

both you guys have made this a good first use of a forum. will defo come back