How to use Range.Select with Offset in Excel? 2

[mattiaturalla]

I am continuing to struggle with a macro that should be a piece of cake. It is supposed to scan down thru a spreadsheet removing parts of rows that have returned #N/A in response to vlookup. I can't select the entire row as there is detail further to the right. The macro to date is;

Range("d4").Select

Do Until i = 75

If Application.WorksheetFunction.IsNA(ActiveCell) Then
Range("ActiveCell.Offset(0, -4), ActiveCell.Offset(0, 3)").Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
i = i + 1
Loop

It is breaking down in the Range.Select line but i can't see what i've done wrong there. Or can you you only use simple single references (eg "A1") in Range.Select?
Any assistance would be much appreciated

 

[SJMcAbney]

I can see quite clearly what you've done. Inside the Range function you've put the ActiveCell info within quotes thus making it a literal string.

Now, what are you looking for in here?

Range(ActiveCell.Offset(0, -4), ActiveCell.Offset(0, 3)).Select

This isn't going to do much yet either as it will return the values of those cells rather that, I presume this is what you want, the cells' addresses.

Range(ActiveCell.Offset(0, -4).Address & ":"& ActiveCell.Offset(0, 3).Address).Select

How's that?

Stewart

 

[mattiaturalla]

Thanks Stewart..but..still no joy. The macro still pulls up at this point. Are the quotation marks correct in your code?

Matti

 

[SJMcAbney]

The only thing I can see that I may have done wrong is a typo where I haven't pressed [SPACE] after the second quotation mark.

Range(ActiveCell.Offset(0, -4).Address & ":" & ActiveCell.Offset(0, 3).Address).Select



Stewart

 

[mattiaturalla]

Thanks again Stewart...still no result. I've never actually selected a range of cells using Range.Select, only individual references. I'm presuming it's the right command - it is isn't it?

Matti

 

[SJMcAbney]

Hmm, it is.

Dim strLeft As String
Dim strRight As String
Dim strRange As String
Dim i As Long

For i = 1 To 75
If Application.WorksheetFunction.IsNA(ActiveCell) Then
strLeft = ActiveCell.Offset(0, -4).Address
strRight = ActiveCell.Offset(0, 3).Address
strRange = strLeft & ":" & strRight
Range(strRange).Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Next i

Substitute your code and step through this bit by bit to see if the range is being built up.

Stewart

 

[mattiaturalla]

Gee if this isn't the most frustrating bit of code. It's broken down at the first line of;

strLeft = ActiveCell.Offset(0, -4).Address

.. mind you it could be giving my street address and i couldn't see the problem i've been looking at the damn thing so long

Matti

 

[TonyJollans]

Hi Matti,

Stewart is making this more complex than it needs to be. His first suggestion just to remove the quotes was all that was required - it will give you the cells themselves and not the values, and should do what you want. To restate it ..

[blue][tt] Range(ActiveCell.Offset(0, -4), ActiveCell.Offset(0, 3)).Select[/tt][/blue]

Whilst his second suggestion should also work, this is only part of your problem.

You start in Column D. You cannot reference a column which is offset 4 columns to the left of column D - it is off the sheet. If you change this to [blue]Offset(0, -3)[/blue], then after you have selected the group of cells and deleted them, the active cell will be [red]A[/red]4 and further selections to the left will fail. I think you want to add a line after the delete, something like ..

[tt] ActiveCell.Offset(0,3).Select[/tt]

Now, after all that, you would be better off not Selecting cells at all, something like ..

[blue]For I = 78 To 4 Step -1
    If WorksheetFunction.IsNA(Cells(I, 4)) Then
        Range(Cells(I, 1), Cells(I, 7)).Delete Shift:=xlUp
    End If
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[SJMcAbney]

Cheers Tony,

I thought the Range("D4") was just an example of what worked as a single cell reference.

I am definitely going to bed now. Been looking at Access and Excel all day.

Sorry Matti.

Stewart

 

[mattiaturalla]

Thanks Stewart for your time and Thanks Tony...gee it's obvious when somebody points it out. the first fix..with the the extra line to get me back to column d works a charm thanks. the second is beyond me..what on earth is it doing? it demonstrably works..from the bottom up.. but i can't see why - sometimes i think this is more art than science.

thanks a million

Matti

 

[TonyJollans]

Hi Matti,

First of all, it is always best to do deletes 'from the bottom up'. If you go top down and delete cells in, say, row 10 then what was in row 11 becomes row 10; if you then increment your pointer (to row 11) and loop you will be checking what was row 12, having missed out what was row 11 altogether.

I'm not sure what you don't understand about the rest of the code but, puling it apart, we see ..

[blue]For I = 78 To 4 Step -1
    :
    :
Next[/blue]

This is a loop controlled by a variable, [blue]I[/blue], starting at 78 first time round and stepping by [blue]-1[/blue] each time to 77, 76, 75, etc. until it reaches [blue]4[/blue] when it stops.

Inside the loop we have a simple condition ..

[blue]    If WorksheetFunction.IsNA(Cells(I, 4)) Then
        :
        :
    End If[/blue]

This checks the cell in Row [blue]I[/blue], Column [blue]4[/blue] for being #N/A!. This is the same check you made except that it uses the [blue]Cells[/blue] method instead of the [purple]Range[/purple] method to reference it. It directly references the cell rather than Selecting it and using ActiveCell so is much more efficient.

Finally the code which actually does the delete which is executed subject to the condition above ..

[blue]        Range(Cells(I, 1), Cells(I, 7)).Delete Shift:=xlUp[/blue]

This references the range of cells to be deleted using the Range syntax (as you did) with the first and last cells of the range being referenced using the Cells syntax as described above. The Range is from the cell in Row [blue]I[/blue], Column [blue]1[/blue] to the cell in Row [blue]I[/blue], Column [blue]7[/blue]. Again, the cells are not Selected; they are simply Deleted.

I hope that helps to take the mystery out of it - it is (mostly) a science

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[mattiaturalla]

Again, obvious when shown. i'd been looking at this problem so long my brain had gone off for a brief vacation. I appreciate the bottom up logic..however.. i have subtotals in column d which now get deleted first. it's certainly a more elegant solution than the 'Select' macro so i'd rather use it. is it possible to put an additional loop so effectively so that the new 'active cell' checks itself again?

Matti

 

[TonyJollans]

Hi Matti,

Ah-a! Yes, subtotals at the bottom will cause you problems!

Have you considered having your subtotals above, rather than below, your values? Having them at the bottom is a hang-over from the days of pen and paper and columns of figures in a book; they are generally more convenient at the top and this kind of problem would also be avoided.

If you can't move them you have a couple of choices. The easiest imay be to check the cell for being a subtotal, perhaps like ..

[blue]If Left(Cells(I, 4).Formula, 9) <> "=SUBTOTAL" Then
    If WorksheetFunction.IsNA(Cells(I, 4)) Then
        :
        :
    End If
End If[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[mattiaturalla]

How clever's that! Thanks - i can see a number of applications for that logic in some other spreadsheets that i can now streamline. Fair comment re the hang-over from days-gone-by but i guess one step at a time for us dinosaur accountants.

cheers

Matti