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
( 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 )Code: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
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/showthrea...ll=1#post14650 ) will help you get closer to what you wanted. )
Alan
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Reply With Quote
Bookmarks