select a range using relative references

[davefish]

Not using VBA for some time I cannot remember how to select a range of cells using relative references.

I have used :-

ActiveCell.Offset(1, 2).Range("A1:B8").Select

but wish to replace ("A1:B8") with a script similar to the offset value as my cell referecne will change file by file. Can someone point me in the direction please?

DaveFish

 

[SkipVought]

Hi,

I would advise against using activecell, select, activate, except for VERY limited purposes. Cell processing is best achieved using explicit references.

This code selects relative to the actice cell, one row down and 2 rows to the right...

ActiveCell.Offset(1, 2).Select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Hi Skip,

What I am trying to achive is to scroll to the end of a variable length list using "Selection.End(xlDown).Select" in column C and then select a range of cells in columns A & B , but underneath the selected row. I need to delete this double column of data in columns A & B but may have data from the end of the selected row to row 1000.

Dave

 

[Gavona]

Skip showed how to reference a single cell. To select a range of cells:
Range(Range1, Range2).Select
Where Range1 and Range2 could be defined as Skip suggested.

So your code could be along the lines of

Set myRange =  ActiveCell.End(xlDown).Offset(1,2)
Set myRange = Range(myRange, myRange.offset(8,1))

Gavin

 

[davefish]

Hi Gavona,

I tried this code and could not get it to work! Skips code ActiveCell.Offset(1, 2).Select was OK, but I could not get your to work!

Here's my code in full. What I'm attepmting to do is not depend on the absolute Range such as Range("A58:B59").Select
Any ideas would be welcome

Selection.End(xlDown).Select
Range("A58:B59").Select
Range("B58").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Columns("N:O").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select
Range("M1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Font.Bold = True
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

 

[SkipVought]

It really depends how your sheet is configured and what you need to do. I can clean up your code, but there is more than likely a better way!

For instance, if the data in columns A & B is 1) contiguous and isolated from other data by an empty column & row, then clearing the data in that range could be...

range("A1").currentregion.clearcontents

    Range(Range("A58:B58"), Range("A58:B58").End(xlDown)).ClearContents
    
    Columns("N:O").Delete Shift:=xlToLeft
    
    Range(Range("M1"), Range("M1").End(xlToLeft)).Font.Bold = True

'I assumed that this table is contiguous and isolated.    
    With Range("M1").CurrentRegion
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davefish]

Hi Skip,

Perhaps I'm being especially dim today and haven't explained myself correctly.

I see you have refered to a cell range "A58:B58" as shown in my code. This was a recorded macro I manipulated, hence the references. The data I am pulling is from a SAP report and is variable in the number of rows from a few A58 for instance to a maximum of say A500. I need to delete spurious cell data in columns A & B but below the last row. To do this I go to the last cell in Column C and offset to the left. select anything in A:B below and delete.

Does that help at all?

Regards

Dave

 

[SkipVought]

I need to delete spurious cell data in columns A & B but below the last row.

Why is there spurious data?

Did it come from the imported file?

Why not clear the entire sheet and then import at A1?

then...

with YourSheetObject.[A1].CurrentRegion
  debug.print "first row",.row
  debug.print "first col",.column
  debug.print "Last row",.row + .rows.count - 1
  debug.print "last col",.column + .columns.count-1
end with

defines the entire table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Is there data "below the last row" that you wish to keep (e.g. in column c or D)?

What cell needs to be selected before you start your macro? If it is always a particular cell then we can hard code it. If it varies then how should the routine identify it? Could you, for example use Find to locate it?


Would this for example get you to the "last row"?
Range("C5").End(xlDown).select

Now consider the offset function to get you to the top left cell that you want to delete:
Range("C5").End(xlDown).offset(1,-2).select

Now extend the selection to include all the cells you wish to delete/clear.
Range(Range("C5").End(xlDown).offset(1,-2),Range("C5").End(xlDown).offset(1,-1).End(xlDown)).Select

If that works then amend to remove the selection and clear the contents:

Range(Range("C5").End(xlDown).offset(1,-2),Range("C5").End(xlDown).offset(1,-1).End(xlDown)).Clearcontents

Gavin