MrExcel.com - Your One Stop for Excel Tips & Solutions

Vis Basic code for choosing cells with Data

Posted by Ben Thompson on May 22, 2001 8:40 AM

What is the Visual Basic language for programming a macro to emulate a control+key arrow function? In other words, in a worksheet with a variable amount of information, how can I have a macro select all cells that are populated with data?

Thanks

Posted by Barrie Davidson on May 22, 2001 10:06 AM

The following code would select cells: A1, B14, C20, and D5.
Range("A1,B14,C20,D5").Select

Does your data reside in the same spot in the spreadsheet or are you looking to determine which cells have data and then select them (the latter would take longer to perform)?

Barrie

Posted by Ben Thompson on May 22, 2001 10:26 AM

The file can be 1 row long, or 1000 rows long.

What I have been having difficulty with is when using a macro to assign a formula that will be applied to every column in the spreadsheet. If I were to run the formula for the entire row (1-65000), it would use up all of the memory on my system. So, I wanted to limit the rows to run the formula on to those that have data. Manually I can accomplish with keystrokes control+shift+down arrow. Any ideas on how to accomplish this in VBA?

Thanks


Posted by Barrie Davidson on May 22, 2001 11:10 AM


The following code should accomplish the same as CTRL+SHIFT+DOWN ARROW (note that you need to declare "End_address" as a variable).

End_address = ActiveCell.End(xlDown).Address
ActiveCell.Range("A1:" & End_address).Select


Does this help you?

Barrie

Posted by Dave Hawley on May 22, 2001 6:40 PM


Hi Ben


Here are another two methods.

Sub LastCell()
Range("A1", Range("A65536").End(xlUp)).Select

End Sub


Sub FirstBlank()
Range("A1", Range("A1").End(xlDown)).Select

End Sub

Barrie, took a peek at you Website last night. Looking good!

Dave

OzGrid Business Applications

Posted by Barrie Davidson on May 23, 2001 6:37 AM

Thanks for the compliment Dave

Dave


Dave, if you have any suggestions for improvement please let me know.

Regards,
Barrie

Posted by Ben Thompson on May 23, 2001 7:31 AM

Re: Thanks for the compliment Dave

I don't know about the web site, but the help on the macro is much appreciated

Posted by Barrie Davidson on May 23, 2001 8:44 AM

Ben, if you're interested

Ben, Dave was talking about my website. If you're interested, the address is
ca.geocities.com\b_davidso

I'd be interested in hearing your feedback as well.

Barrie