Would like a Macro to Select a Variable Area

Rod_Bowyer · #1 03-29-2016, 11:01 PM

I hope that it is ok to post these requests.

I also hope that this is an easy one - but for a novice like me it is difficult.

I have data in columns G to R starting at cell G19. The number of rows varies each time I bring in a new record. I would like a macro which would highlight the area from G19 through to R "whatever number" ("whatever number" will vary each time I run the macro).

When I record a macro I go from G19 end down and then select right until column R. Unfortunately the macro "anchors" itself according to how many records there are in Column G at the time I write the macro. The next time that I run the macro with more records in it it only highlights the previous area.

I hope that I have explained this ok

Many thanks - Rod

Philb1 · #2 03-30-2016, 02:44 PM

Hi Rod
Try this

Code:

Option Explicit

Sub SelectAnArea()

Dim Ws As Worksheet
Dim Lrow As Long
Set Ws = ThisWorkbook.ActiveSheet

'   Find bottom row number in columns G:R
    Lrow = Ws.Columns("G:R").Find(What:="*", LookIn:=xlValues, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlPrevious, _
        MatchCase:=False, searchformat:=False).Row

Ws.Range("G19:R" & Lrow).Select

End Sub

Rod_Bowyer · #3 03-30-2016, 04:56 PM

Hi Phil,

Absolutely perfect.

Many thanks - Rod

ps - I have bought a couple of books on VBA programming so (hopefully) I wont need to bother you so much in the future.

Philb1 · #4 03-30-2016, 07:22 PM

Hi Rod
I do it coz I like it, it's like returning something to the universe after so many people helped me when I started out. I've been playing with spreadsheets out of interest, I don't work with them & everything I learned has come from search engines, forums & experimentation. I find I prefer macros over formulas. Macros are more logical to me plus you can do fun stuff with them

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Would like a macro to copy a cell down a variable number of rows	Rod_Bowyer	Excel Programming	6	03-25-2016 08:18 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Select a area in a Word Document	Peter Stahl	Word VBA	2	08-09-2013 03:18 AM
Using macro to add variable number of rows to a protected word table	Julia	Word Tables	1	01-09-2013 06:04 AM
Help with VBA macro - Variable input	sc30317	Excel Programming	0	08-31-2011 01:00 PM