Range Of Cells Of Known Columns But Unknown Rows

Xanosect · Mar 18th 2008

I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.

What I've managed to find so far on the forums is

Code

Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select

which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).

**norie** · Mar 18th 2008

Re: Selecting A Range Of Cells Of Known Width But Unknown Length.

You don't use Offset like that in VBA.

In fact the OFFSET worksheet function and the Offset property in VBA are totally different things.

There are many ways to find the last row of data.

Here's one.

Code

LastRow = Range("N" & Rows.Count).End(xlUp).Row

How to use this, or any other method, for your purposes is hard to tell because it isn't actually clear what you want to do.

Simon Lloyd · Mar 18th 2008

Re: Selecting A Range Of Cells Of Known Width But Unknown Length.

The whole range can be found like:

Code

Sheets("Sheet1").Range("A2:N" & Range("N" & Rows.Count).End(xlUp).Row)

or the last row in Column N like this:

Code

Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp)

to find the last but one row add

Code

.Offset(-1, 0)

to find the last used column its like this:

Code

Range("IV1").End(xlToLeft).Column

hope these help as i didnt understand what you were after, finally if you want all rows selected except the top and the last then use:

Code

Sheets("Sheet1").Range("A2:N" & Range("N" & Rows.Count).End(xlUp).Row).offset(-1,0).Select

Xanosect · Mar 18th 2008

Re: Selecting A Range Of Cells Of Known Width But Unknown Length.

editted in hopes of providing more clarity on what I'm trying to accomplish. If a specific section isn't clear let me know which so I can expand.

I think that last option from simon is very close if not exactly what I need.... haven't gotten it to work yet but it sounds like it's what I need.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]So the line

Code

Sheets("Sheet1").Range("A3:N" & Range("N" & Rows.Count).End(x1Up).Row).Offset(-1, 0).Select

is generating a run time error 1004, application definded or object defined error. I've copy and pasted directly. Anyone know what I'm doing wrong?

Dave Hawley · Mar 19th 2008

Re: Selecting A Range Of Cells Of Known Width But Unknown Length.

Code

With Sheets("Sheet1")


Application.GoTo .Range("A3", .Cells(.Rows.Count,"N").End(x1Up))


End With

jamiwr · Apr 17th 2008

Re: Range Of Cells Of Known Columns But Unknown Rows

I have used the VBA and it works to select all data but I have an issue when there is no data to select beneath the column header. I am running this on multiple worksheets which vary with data week to week.

Code

Sheets("sheet1").Range("A6:G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(0, 0).Select
        selection.ClearContents

How can I run this and not have it delete the column header when there is no data?

Thanks

**norie** · Apr 17th 2008

Re: Range Of Cells Of Known Columns But Unknown Rows

jamiwr

How is this connected to the original thread?:confused:

And why are you even using Offset when you aren't actually offsetting either rows or columns?

Please start a new thread.:)

Range Of Cells Of Known Columns But Unknown Rows

Participate now!

Share