#1
|
|||
|
|||
Would like a Macro to Select a Variable Area
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 |
#2
|
|||
|
|||
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 Last edited by Philb1; 03-30-2016 at 02:46 PM. Reason: End Sub was missing |
#3
|
|||
|
|||
Thank You
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. |
#4
|
|||
|
|||
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 |
Tags |
macro - variable area |
|
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 |