Range / Selection "In between cells" (VBA)

**Zenarte** · 03-17-2010, 06:22 PM

Hello, I'm having quite a headache trying to figure out this problem... I'd be pleased if someone would be able to help me.

I created this macro, where all the info related to this frame sheet is copied to a different one every month, and then all the data on the frame is erased, so a new month can be started.
The problem is that I need to copy the name of the current month to a certain number of lines on the first row of this secondary sheet (since every other line on the frame sheet doesn't have any direct connection with it), so that way I'll be able to use the Index formula (using two conditions).

What I want to know is:

If I estabilish a mark in A1 and another one in A70, will I ever be able to use the "Range(Selection, Selection.End(xlDown)).Select", then go "ActiveCell.Offset(-1, 0).Select", and then go all the way back ("Range(Selection, Selection.End(xlUp)).Select" /
"ActiveCell.Offset(1, 0).Select") keeping all those cells in between selected, without having to define any estabilished range (so then I may copy the name of the current month into these)?

I have to do that, because every month new items may be added to the frame sheet, so any estabilished range would have to be repaired manually (which definitelly means headache).

If anyone can help me, I'll be very happy. Thank you!

**6StringJazzer** · 03-17-2010, 09:05 PM

I have read your post a few times and still can't figure out exactly what you're trying to do. It would help if you posted your existing code, or better yet your workbook.

An important tip about VBA and Excel: When you record a macro, it tracks your selections by each step. But your code doesn't have to use selections to do the job. For example,

Code:

Range("A1").Select
Selection.Value = var
 
 ' can be replaced by
 
Range("A1") = var

Selections are time-consuming and can obfuscate code.

If you need to replace all non-blank cells in column A with the same value, you can do this:

Code:

Dim LastRow as Long
LastRow = Range("A65536").End(xlUp).Row
 
Dim CurrentMonthName As String
CurrentMonthName = MonthName(Month(Date))
 
Dim c As Range
For Each c in Range("A1:A"&LastRow)
   c.Value = CurrentMonthName
Next c

Here are a couple of things I can't figure out from your post:

I don't know what you mean by "establish a mark."

You specifically mention A70, but is that just an example, since it sounds like you just need "a certain number of lines"? How do you determine the number of lines?

**Zenarte** · 03-17-2010, 10:00 PM

Hello 6StringJazzer, thanks for your reply!

I was reading your reply now, and then suddenly I had this crazy idea of adding a "Do While" on the formula. It worked pretty well, but I believe that there's an easier way to do that (after all, on my main sheet there are around 70 lines, and each one of them is linked to this huge sheet, compiling every single info with that Index formula, so it takes quite some time until every single cell is written).

I'm sending an example attached to this e-mail! Oh well.. And then all of a sudden, I got another headache that I didn't think of it before.. Is there any way that I can turn that copied range into a dynamic range? Like, if I add new products, Excel will recognize this new line, and won't forget to copy it, without needing me or anybody else to change the macro.

Thanks again!