Selecting a group of cells using the Range.offset syntax

[JZB]

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

[VBA]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[/VBA]

[JONvdHeyden]

Is your currency EUR housed in column E?

[JONvdHeyden]

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.

[vba]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[/vba]

[JZB]

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]

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:
[vba]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
[/vba]

Hope this helps,

Mark

[JZB]

thanks. will give this a try

[GTO]

@Jon:

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

Have a great day,

Mark

[JZB]

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]

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]

thanks Mark

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