Macro to Count below and drag across

[Wall31]

Hi everyone

I recorded a macro and was wondering if someone could help me refine it or show me a better way of doing it.

I need to insert an empty row 2.
Then I need to count the non empty cells from row 3 through to a row number whose value is 5 added to a number taken from cell A3 in Sheet3.
For example, if the value of A3 in Sheet3 is 13, then we need to go until row 18. I don't know how to code this, so I just typed A18 directly into the cell (which translated to R[16]C in my recorded example below).
Then I need to fill-drag the formula as far right as I need to go (determined by how many columns the current sheet has).
In the example below, I've gone up to Column M but in some cases, I may need to go up to Column AC.



Sub Macro1()
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[1]C:R[16]C)"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:M2"), Type:=xlFillDefault
Range("A2:M2").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub


My "solution" is very clunky and there is likely a neater way around this so I apologise in advance for the code mess. I would need to learn how to code this nicely.

Can someone help me with this? Thanks!

[DocAElstein]

Hi
I think this is easy to do, and I can probably show you a few ways to do it, both by
refining your macro recorded coding,
and
other ways starting from scratch.

But it will be an awful lot easier for me if you can upload two workbooks.
One showing Before and the other showing After you ran the macro.
Then I can write you coding, check the results and return a workbook.
Otherwise I have to make up a workbook myself, and I don’t have the time

( By the way, if you ever need urgent help, its best to ask at the major forums, such as excelforum.com or mrexcel.com. There are a lot helpers there 24/7
I will answer all questions I can here, but I only pop by here once in a while. ( Currently not many other people pop by here. ) )

Alan

( Important: Most forums have a “Cross post” Rule – you must tell everyone everywhere when you post the same question at different forums )

[Wall31]

Thanks so much for this Doc!

I've run the macro on the the "End" sheet. Hope that makes sense!

Thanks again

[DocAElstein]

Hi
Things are not 100% clear to me yet…
_ I don’t see any Sheet3 in your uploaded file?
_ The macro in your workbook is different to that you gave in post #1, and it would be helpful to know exactly what you did as well when running the macro recorder.
Never mind. I think I can figure out though the main jist of what you want.


Regarding the macro recorder
I personally think it is a very good tool for a beginner to use. Even Professionals use it to get a start or to get a specific syntax, since it is almost impossible to know/ remember all the different syntaxes.
But, the important thing to remember is that it simply tries to do all you do and make a cell be like you make it or have it.
So I would say there are three main things to tell you about your macro regarding making it unchunky

_1. This point is typical of almost all macro recordings. We, as Humans, need to see and select/ activate cells to do anything. Excel and Excel VBA almost never needs to select or activate. So almost always things from a macro recorder with two code lines like pseudo .._
RangeX. Select /Activate_It
Selection/Activated_Bit.Do_Something
_.. can be replaced by a single code line
RangeX.Do_Something

_2. Filing down, dragging down, and similar things in Excel is a convenience made specifically for us Humans. The macro recorder will reproduce it because you did it. From a programming VBA point of view that is all a “weird” complicated thing that really makes no sense. It is very rare that as a programmer you would ever want VBA to do something “weird” like that. It is a very inefficient unnecessary thing, similar to selecting and activating

_3. I will take a guess that you only wanted to make the format in the inserted row Bold and Centered
Excel VBA does not know exactly what you wanted, and often if you make one change to a cell , or just a few changes to a cell, the macro recorder then makes the coding to give the format of the cell finally. This means it may chuck up some redundant coding to give a format option that was already there. That means some code lines you can just remove. Because I don’t know exactly what you did, I can only guess a few things to remove. For example, I doubt very much that you un merged any cells, since probably there were no merged cells. So you can remove .._
__.MergeCells = False
_.. since that code line just makes the cell un merged, which they most likely already were.


So those above are the general points, now specifically:

_(i) _ Insert a row.
I cant improve much on that, other than point _1.
We can change .._
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
_.. to
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

_(ii) The next point is related to point _2. , but also needs some understanding of something very fundamental to Excel
First, we can forget the filling down. The ability to drag or fill down is an action designed for Human convenience. This action rarely makes any sense to a logical computer program. Computer programs would not have a ( low ) limit on how many lines could be filled at, or almost at, once. ( We don't necerssarily need to loop to fill in many cells: It will depend on what we want to do).
Second, something very fundamental to Excel, ( which surprises me time and time again that hardly anyone seems to understand it fully).
Your formula can be written in two ways
=COUNTA(R[1]C:R[8]C)
or
=COUNTA(A3:A10)
That is exactly the same formula as far as Excel is concerned
It is a relative formula, or put better a “fixed vector” formula ( https://teylyn.com/2017/03/21/dollarsigns/#comment-191 )

Like many things in life, if you understand the most basics fully, ( which in Excel VBA so far I only know about half a dozen people who do ) , then everything else is easy, just applying that.
All you need to do is either drag, copy, write, or paste that same formula in every cell. It is as simple as that! Writing in one go is usually the fastest for Excel VBA to do.
Specifically how to do that:-
In default settings in Excel, ( which I assume you have) , the “seen” thing in any cell is the thing held by VBA as the .Value Property. That is reasonable clear I think.
Possibly less clear, ( at least up until now, I never met anyone that understands this, apart from me ) , what is written into a cell by a human is correspondingly what VBA “does” when you apply in coding
________.Value
to a cell or range of cells.
In other words
Human write in a Range “This” = VBA code line RangeX.Value = “This”

You simply need a single code line to write in your “fixed vector” formula in the entire range:
Range("A2:F2").Value = "=COUNTA(A3:A10)" ' or = "=COUNTA(R[1]C:R[8]C)"
( It is/ will be the same formula in every cell, even if it in the spreadsheet may be represented in each cell differently by Excel :- This last point is a very simple but subtle point, which is understood by almost on one: I think Microsoft deliberately used a confusing terminology and syntax to confuse everyone, and make things look more complicated then they are).

_(iii) Your cells format
As I said, I will take a guess that you only want Bold and Centerd.
So all that formatting coding can be reduced to like
Range("A2:F2").Bold = True
Range("A2:F2").HorizontalAlignment = xlCenter
You may need to tweak that a bit if you wanted some of the other stuff.


Finally, a first stab at the simplified coding is

Sub Macro1_a()
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2:F2").Value = "=COUNTA(A3:A10)" ' or "=COUNTA(R[1]C:R[8]C)"
Range("A2:F2").Font.Bold = True
Range("A2:F2").HorizontalAlignment = xlCenter
End Sub

' Or using With End With
Sub Macro1_a_With_EndWith()
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Range("A2:F2")
.Value = "=COUNTA(A3:A10)" ' or "=COUNTA(R[1]C:R[8]C)"
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
End Sub
( You can use the With End With if you like. That is personal choice. I personally don’t like using it much. Personal choice, that’s all )


That is not quite your final form that you want.
You might be able to figure out the rest yourself***. If not , then explain again to me both.._
_.. exactly what you want
and
_.. exactly what you actually did when doing a specific macro recording.

( *** I think your previous Thread ( https://excelfox.com/forum/showthread.php/2592-Cell-Value-to-become-part-of-RC-in-macro?p=14650&viewfull=1#post14650 ) will help you get closer to what you wanted. )


Alan







https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)