Excel97 Code neede--Select entire range 4

[MrsTFB]

Hey guys,
I need some code to select the range of active cells. By active cells, I mean the block of cells used. This will vary from worksheet to worksheet, so the exact range cannot be named.

I can record macro, however it names exact range, not only finding last row and last column.

I'm sure someone has this code. I just am not that great at VBA.

A little help please.

Thanks in advance,

mrsTFB in Tennessee

 

[MrsTFB]

I think I should have placed this thread in the VBA section. How much trouble do you get in if I also place it there.

What do you guys think?

mrsTFB in Tennessee

 

[Dreamboat]

You'll be fine right here. The Excel experts are here.

Meanwhile, see this:

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

(I'm not a coder, but it may help until someone answers with a REAL answer.)

http://www.TheWordExpert.com

 

[SkipVought]

Hi,

There are two ways to consider in selecting a range of cells...

1. CurrentRegion defines a CONTIGUOUS range

2. UsedRange defines a rectangular range encompassing every range on a sheet

VBA Help has some more info regarding each of these.

So, if you knew that rng was within the contiguous range of data you wanted to reference, then...

With rng.CurrentRegion
   FirstRow = .Row
   FirstCol = .Column
   LastRow = .Rows.Count + FirstRow - 1
   LastCol = .Columns.Count + FirstCol - 1
End With

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[MrsTFB]

I want the entire range. I'm not sure I described it well, there is no named range. I want to define the entire set of cells that contain data.

I tried the above code and it is looking for the range rng

Thanks, but I need more help!

 

[SkipVought]

rng referrs to any range that you have currently defined, like a single cell. It could be Range("B4&quot, or Cells(5,9)

IT WORKS!!! Skip,
metzgsk@voughtaircraft.com

 

[acron]

Try

ActiveSheet.UsedRange.Select

As a matter of interest, do you really need to select the cells. You can process cells in VBA without actually causing them to be selected.

A.C.

 

[SkipVought]

You know that you do not have to "Select" a range to determine its properties or change its properties. In fact the only way to "Select" a range of cells is to have the sheet acitvated. When you are working with many sheets, VBA works alot faster if each range object is set in the form...

Set rng = Workbook.Worksheet.Range

Then you dont have to activate workbooks/sheets and select ranges....

SSSSLLLLLLOOOOOO

http://WWWWWWW!

Skip,
metzgsk@voughtaircraft.com

 

[MrsTFB]

You guys are great! What I am going to have to do is copy the data on one worksheet into another worksheet. The amount of data will change each month, so I can't exactly say what range to copy and also I do not want to select any empty lines beneath the data.

Does this make sense?

Let me know!

Thanks,
MrsTFB

 

[xlbo]

Try this
Sub Used_CR_Copy() 'counts number of columns & rows used
usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
'This seems to reset the usedrange - making it accurate
Activesheet.usedrange.copy destination:=Sheets("YourSheet&quot.Range("A1&quot
application.cutcopymode=false
End Sub
Watch for word wrap Sheets("YourSheet&quot.range("A1&quot is all 1 string. Everything from Activesheet to ("A1&quot should be on 1 line
HTH
Geoff

 

[MrsTFB]

That was perfect xlbo!!! Exactly what I wanted! You deserved a star!!! I gave you one.

Thanks for all your help guys!

I love this website!

mrsTFB

 

[MrsTFB]

Help,
Just a smidgen more help!!! PLEASE!! It's working perfectly, except, the second workbook will already have data in it and I need to find the last row used and place the data just after that. Is that append to the existing data. I know there is code for that also. I have the workbook names and sheet names worked out, but I need it to go to the last line of data and paste right after that.

I hate to be a bother, but this report has to be sent tomorrow and it's not doing what I want yet.

Thanks for your patience,

mrsTFB in Tennessee

 

[DaleWatson123321]

This is one option...

Sub Go_Last_Row()
'Goes to NEXT BLANK row in Column A
' - assumes Column A has contiguous data.
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, 0).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
End Sub

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Just use :

Sub Used_CR_Copy() 'counts number of columns & rows used
lastRow = Sheets("WorksheetToAppendTo&quot.range("A65536&quot.end(xlup).row
usedrows = ActiveSheet.UsedRange.Rows.Count
usedcols = ActiveSheet.UsedRange.Columns.Count
'This seems to reset the usedrange - making it accurate
Activesheet.usedrange.copy destination:=Sheets("WorksheetToAppendTo&quot.Range("A" & lastRow)
application.cutcopymode=false
End Sub

HTH
Geoff

 

[DaleWatson123321]

Geoff,

I agree with your method - it's better and more "complete".

===> STAR <===

Thanks for the contribution !!!

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Guest_imported]

Do you now a site Where i can found the US version of Office 97

 

[Dreamboat]

Ninette: Please ask your question separately next time.

What do you mean by finding Office 97? What is it that you need to know? It is not possible to download the Office Suite, if that is what you're looking to do, at least not legally. And if an illegal place existed, the rules of this website disallow us from assisting you from procuring software illegally.

Hope that helps, let us know!

http://www.TheWordExpert.com

 

[ProgammingGreece]

Hi there,

How can I make a sheet to be activesheet so I can run an SQL stmnt.

Thanx
sdk

 

[xlbo]

sdk - did you not see Dreamboats message to Ninette ???
Start a new thread
a: You are more likely to get a response
b: The integrity of the thread is not comprimised for searches

Start a new thread and I will answer your question
Rgds
Geoff

 

[fkegley]

What about sheet-level range names?
Select the cells you want to give a name to.
Click the name box.
Type the sheet name (in [], if sheet name has spaces in it)
an exclamation,
then range name.
Sheet1!Range1 as an example
In this way each sheet can have a range of the same name.

I still remember the day I discovered this