Center across selection formatting Results

Hi all,

I have this table and I am having troubles in retrieving values from a centered across selection row.
Cells are not merged, it is just a simple center acrosse selection that has been used.

What I want to do is for example to get my macro to find the column X which is the first column to match my 3 criteria (in this case Year To date, Budget and Feb/07).
I use these criteria to test this set of lines (line 20 to 22) for each column.
It works fine for a table without any special format but I have troubles with the "center across selection" format because the cell X20 is empty. The only cell that has a value in U20:AA20 is the U20 which has the "Year To Date" value.

How do I get my macro to find out that even if the cell value is null, the "real" value is "Year To Date" ?

I do not really want to manually edit this file to change this format to something else.

I hope my explanation is clear enough.

Many thanks in advance for your help.

Bowman.

ps: I am running excel 97 SR2.

I'm querying an excel file from within AutoCAD VBA. I'm iterating over the cells

row by row. When I come across a cell that has Horizontal Alignment=Center Across

Selection. I need a way to tell how many cells the selection is centered across.

I've searched everywhere for this. Can anyone help?

This is the general way I'm working with the file.

	VB:
	
 sched.ActiveSheet 
     
    For r = 1 To rows 
        For c = 1 To columns 
            If (.Cells(r, c).HorizontalAlignment=7) '7 =Center Across Selection
            ......do stuffff 
        End If 
    Next c 
Next r 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How can you query to get the number of columns 'Center Across Selection' is

occupying?

Thanks in advance!

I'm using Excel 2008 and would like to have a series of cells that may have a value by a calculation, or they may be empty. In the case where they are empty, I would like to be able to use the "Center Across Selection" feature. Normally for blank cells, I just use two sets of quote symbols so nothing is displayed, but in this case, I would like to center over that selection. Is there any way to do this?

If the context is useful, I am trying to highlight when periods occur in a project plan. For some efforts, there may be 12 month periods, 6 month periods, etc. In the below picture, I have conditional formatting working above the text to show the bounds of each 5 month period, and I have the number of the period in the first month of that period. I would like to change the text to "Period "&#. If I do this, a good portion of the text will be truncated, but if I could center across selection, the formatting would be improved.

Any help is much appreciated!

I'm sure others must have had this problem. Hopefully someone can help me
fix it. When I format a cell as center across selection, it centers the info
over whatever area I had highlighted. But once I've done it once, I can't
figure out how to change the selection to center the text over.
For example, let's say I center A1 over A-H, then I delete the information
in column G and H and I only want to center the info over A-F. No matter
what I do I can't seem to change the selection.
I've tried highlighting the new area, and hitting "center across selection."
I've tried deleting the info and reentering it. I've tried left alighning
it, then centering it again. No matter what I've tried, I can't seem to
change the selection my text centers to. Thanks for the help.

Hi,

I use Excel 2007 at work and Excel 2003 at home.

Is there a keyboard shortcut to center across selection for either of these
versions, pref 2007?

And secondly, does anyone know if there is a toolbar button for center
across selection, or perhaps a way of placing a button on the quick access
toolbar?

Merging cells is too easy to do and that is why I prefer it, however
formatting and selecting cells is becoming an issue with merging, and center
across selection has it's merits, but it's time consuming.

Thank for the advice, I hope someone can help.

Regards,

Rajen

Can you center acorss selection vertically? I'd like to distribute a label
across 2 celss vertically adjoined to each other. I do not want to merge
them.

tia,
Dave

Is it possible to add the format command "Center Across Selection" to the Quick Access toolbar in Excel 07'? I have searched and searched but unable to find it or maybe im just overlooking it.

I could just make a custom macro or something but would rather just use the built-in features if possible.

Hi,
I would like to know if there is a way to center across a selection in vertically. I know the center across selection horizontal option is in the format cell menu but the vertical equivalent is not there. Does it exist?

Thanks

The next version of Excel should allow the user to format a cell to "left
align across selection" in addition to just "center across selection." This
will be useful in situations in which a cell contains multiple lines of text
when "wrapped" and the user does not want to merge cells to fit the text.
The current option of "center across selection" allows the user to refrain
from merging cells, but is not ideal for paragraph block text passages.
"Left align across selection" will allow text to be displayed in normal
paragraph format without having to merge cells and disrupt columns.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

The next version of Excel should allow the user to format a cell to "left
align across selection" in addition to just "center across selection." This
will be useful in situations in which a cell contains multiple lines of text
when "wrapped" and the user does not want to merge cells to fit the text.
The current option of "center across selection" allows the user to refrain
from merging cells, but is not ideal for paragraph block text passages.
"Left align across selection" will allow text to be displayed in normal
paragraph format without having to merge cells and disrupt columns.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I was following a tutorial and read that it may be a poor habit to Merge Cells. I know it is impossible to copy and paste special (formulas, values, etc) if there are any merged cells in the range.

I have been using the Center Across Selection ever since I read that piece of information. The alignment is apparently only to be centered on the selection range.

Quite by accident I found out that if I format the selection range as an accounting format, the contents are actually right aligned across the selected range, without using the cell merge. I am sure this has to do with the custom format
Now my question:  Is there a way to create a custom date format so that the date is right aligned across the selection.

I have a heading, Columns B thru K, text in B and centered across selections.

No matter what I try I can't really get rid of the range selection.

If I undo the format, alignment. OK
Then go and retry it except to B thru F, it reverts back to the B thru K ???

Excel 97 and XP ?

This time it's really weird, I deleted the entire row, and went back in.
It still remembers the range ??

--
KlK, MCSE

Without looking at any of your macro, and only noticing this error:

Error 1004 This operation requires the merged cells to be identically sized.

My first suggestion would be to dump the merged cells (use center across
selection if you can).

Then see what happens.

If that doesn't help, you may want to start a new thread. (Sometimes followups
get lost.)

Richard wrote:
>
> Hello Dave,
>
> Many thanks for your prompt response and concise
> explanation to my problem.
> You were spot on. The check merged cells was ticked and it
> would appear this was causing the problem. Also your macro
> to format the combo box cell worked fine. Thanks for all
> that.
>
> Dave, could you help me with another problem. Below is a
> macro that was working fine until I reorganised my
> spreadsheet and changed cell references in my macro. I've
> stuffed something up and I don't know what. I have no
> backup of my original code as I had a major computer
> failure whilst doing this stuff and I've lost track of it.
>
> The macro just gets a common number in cell M6 and
> together with numbers in cells C15 to L15, lists them in
> three separate columns starting in D75, E75 and F75
> downwards. Usually goes to about row 175 but can go
> further as more combinations are needed. The user
> selection of of A B or C determines which column has the
> common number.
> Example:-
> ColD ColE ColF
> SelA SelB SelC
> Row75 9 4 6 4 9 6 4 6 9
> Row76 9 3 6 3 9 6 3 6 9
> Row77 9 2 6 2 9 6 2 6 9
>
> It is crashing at the line at the botton of the macro
> starting with... Range("D75").CurrentRegion.Sort to x
> 1Topto Bottom. The message is:- Error 1004 This operation
> requires the merged cells to be identically sized. The
> macro is putting the data in rows C D and E which are the
> columns I had originally but I have changed the code to be
> D E and F, which is where I want my data entered now.
>
> When I enter B or C, columns A and B are being deleted and
> I have vital data in them. Can't see how it is doing this.
>
> Could you have a look at it if you have the time. I've got
> something tangled up.
>
> Thanks for your help.
> Regards,
> Richard
>
> Code:
>
> 'A M6 value is on the left
> 'B M6 value is in the middle
> 'C M6 value is on the right
>
> Option Explicit
>
> Sub Permutations()
>
> ActiveSheet.Unprotect
> Application.ScreenUpdating = False
>
> Dim arr(0 To 9) As Long
> Dim arr1(1 To 3) As Long
> Dim cnt As Long, varr As Variant
> Dim j As Long, i As Long, k As Long
> Dim m As Long, temp As Long
> Dim sType As String
>
> '
> ' set sType to "A" or "B" or "C"
> '
> sType = InputBox("Enter A, B or C")
>
> varr = Range("C15:L15")
> arr1(1) = Range("M6").Value
> j = 75
> Range("D75").CurrentRegion.ClearContents
> For i = 1 To 1023
> bldArr i, arr, cnt
> If cnt = 2 Then
>
> m = 2
> For k = 0 To 9
> If arr(k) = 1 Then
> If varr(1, k + 1) = 0 Then
> Exit For
> End If
> arr1(m) = varr(1, k + 1)
> m = m + 1
> End If
> If m = 4 Then Exit For
> Next
> If m = 4 Then
> Cells(j, 3).Resize(1, 3).Value = _
> Arrtype(arr1, sType)
> j = j + 1
> temp = arr1(2)
> arr1(2) = arr1(3)
> arr1(3) = temp
> Cells(j, 3).Resize(1, 3).Value = _
> Arrtype(arr1, sType)
> ' Crashing here
> j = j + 1
> End If
> End If
> Next
> Range("D75").CurrentRegion.Sort _ 'Crashing here & next 6
> Key1:=Range("D75"), Order1:=xlDescending, _
> Key2:=Range("E75"), Order2:=xlDescending, _
> Key3:=Range("F75"), Order3:=xlDescending, _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, Orientation:= _
> xlTopToBottom 'Crashing here
>
> Application.ScreenUpdating = True
> ActiveSheet.Protect DrawingObjects:=True,
> Contents:=True, Scenarios _
> :=True
>
> End Sub
>
> >-----Original Message-----
> >#1. I've never had any problem just selecting the rows
> (and just those rows)
> >that I need to adjust. Are you selecting more than you
> should? (same for
> >columns).
> >
> >#2. It sounds like you merged the cells, too.
> > select that range f70:h70
> > format|cells|Alignment tab|uncheck merged cells
> >
> >#3. I put a combobox from the controltoolbox toolbar on
> my worksheet.
> > I doubleclicked on it and saw the spot where I could
> add this little macro:
> >
> >Option Explicit
> >Private Sub ComboBox1_Change()
> > Me.ComboBox1.Value = Format
> (Me.ComboBox1.Value, "00.00")
> >End Sub
> >
> >(And I had to go into design mode first (an icon on that
> controltoolbox
> >toolbar).)
> >
> >(by the way, the dropdown from the Forms toolbar kept the
> format of the input
> >range--I didn't have to do anything special.)
> >
> >Richard wrote:
> >>
> >> Hello,
> >>
> >> I've upgraded from Excelv7 for win95 to Excel97 and
> would
> >> appreciate if anyone could help with these questions.
> I'm
> >> finding it difficult to now do some things that were
> >> second nature to me in the previous version. It's very
> >> frustrating.
> >>
> >> 1 How do I increase or decrease column or row sizes
> >> several at a time. I could do it in 95 but when I do it
> in
> >> 97, ALL my previously sized rows or columns revert to
> the
> >> one new size. I've had to resort to resizing one at a
> >> time. Surely this can be done in a similar way to
> before.
> >> I highlight all the rows I want to change the height for
> >> and select the size, but all my other rows change to
> this
> >> value and I have to go back and redo them all again.
> Must
> >> be doing something dumb.
> >>
> >> 2 I've used the Fill color to format my cells F70 to
> H70.
> >> I then centered the text across the rows just like I
> used
> >> to do. Now I can't delete the formatting of these cells.
> >> In fact I can't put the cursor on G or H70. How do I
> >> reclaim these cells? If I try to copy another cell
> over, I
> >> get a message about different sizes and shapes.
> >>
> >> 3 I have a combo box on my spreadsheet, which I use to
> >> pull in times in the format of 12.30. My list is
> formatted
> >> as two decimal places and my target cell is also
> formatted
> >> in two decimal places. However, when I click the drop
> down
> >> arrow, I can see all the times formatted as two decimal
> >> places but after I click on the desired one it is
> >> displayed as 12.3. I can't get it to display the two
> >> decimal places. I can format the cell fonts but there is
> >> nothing I can see about decimal places. How do I do
> this?
> >>
> >> Apologies if these are elementary questions but I'm
> >> stumped.
> >>
> >> Please!
> >>
> >> Regards
> >
> >--
> >
> >Dave Peterson
>
> >.
> >

--

Dave Peterson

When I copy multiple rows (name, address, city) from 3 cells into 3 merged
cells(in rows), I get name name name in the first line, and the same
repetitions in the second and 3rd lines. I've tried format, cell, center
across selection, but get the same results. When I copy just the name cell
to the merged cell, it works fine. I don't want to have to copy each of the
three cells and paste them individually. I want to select all three, and
paste them in the merged rows.

Any ideas how to work around this merged cell problem?

I was using excel and tried centering the name of my table across a selected
amount of rows, which normally works but it just would not centre properly.
Can anyone help me figure out what I am doing wrong? I went into cell
format, then text alignment, chose Horizontal and clicked on centre across
selection but it just won't centre properly.

Would really like your help in solving this problem.

Inspired by Peltiers wonderful Dynamic charting tutorial
<http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html>, I
started to wonder how feasible it would be to create a set of rules
that would allow me to dynamically create a table based on exported
data of variable row count, column count, etc. I'm not really 'fishing
for code' here, as much as fishing for insights on how this would play
to automation/excel's strengths and weaknesses.

By table, I mean a presentable table ultimately destined for inclusion
in a printed report - thin borders for individual cells, thicker
borders denoting groups of information, merged centered column headers,
etc.

Currently I export the results of a crosstab query from Access 2002
onto an Excel 2002 worksheet, let's call it "SourceData". Another
worksheet ("Tbl4Export") contains formatted tables linked to the data
on "Sourcedata."

The data are very variable - It involves schools surveyed over the span
of several years, broken down by grade.

A typical table might look like (this is a stripped down version, so
text wrapping doesn't bone me; it could have up to 4 grades)

"Sch" = School Data
"St" = State Data
02 = Year 2002 etc.
Var = Variable
Pretending A1:I1, A7:I7 has an uninterupted border.

A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 |
3| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
4|Var1 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
5|Var2 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
6|_____|_______________________|_______________________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 |
9| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|_____|_______________________|_______________________|

But say the School has missed a couple of years in the survey or didn't
do all grades one year, and also had an extra variable they were
measuring:

A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 | Grade 9 |
3| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
4|Var1 | 6.0| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2| 9.3|
5|Var2 | 4.0| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2| 8.1|
6|_____|_________________|_________________|___________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 | Grade 9 |
9| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0*| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|Var5 | | 1.5| 2.5| n/a | 6.0| 6.9| 3.2| 5.2|
13|_____|_________________|_________________|___________|
14|*Data categories var4 and var5 combined in year 2003 |
15|_____________________________________________________|

~On the Sourcedata sheet, the original data looks like this:

A B C D E F G H I J
1 Sec1 Section Description 1
2 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
3 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
4 v1 Var1 6.0 7.5 7.2 8.0 7.5 7.6 9.2 9.3
5 v2 Var2 4.0 4.5 5.2 5.0 6.5 5.6 7.2 8.1
6 Spacer
7 Sec2 Section Description 2

8 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
9 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
10 v3 Var3 6.0 6.5 7.5 7.2 8.0 7.5 7.6 9.2
11 v4 Var4 4.0* 3.5 4.5 5.2 5.0 6.5 5.6 7.2
12 v5 Var5 1.5 2.5 n/a 6.0 6.9 3.2 5.2
13 Spacer
14 v45lbl *Data categories var4 and var5 combined in year 2003

Note that in column A, I have some variable codes that can be used to
help differentiate/demarcate sections (i.e. "grLbl" indicates that row
contains grade labels).

Are there some elegant, easily adaptable approaches to "dynamic
tables"?

I'd love to put together a set of rules in VBA to programatically
address stuff like (a hypothetical list):
A)merge/center-across-selection the grade columns, apply heavy outline
to grade section borders, as appropriate (how to detect what defines a
"section"? Identical cell values?)
B}Make Section description rows grey, while
B)Always make the state data column yellow
C)Conditionally center some data (i.e. the "n/a" for variable 5 in
2002)
D)Separate each sections by a heavy border (i.e. on the second example,
the 6th grade data would have heavy border around ranges B2:D2, B2:D6,
B8:D8, B8:D13)
E)Adjust column width/row heighth to compensate for charts with fewer
columns (so sizes still mesh nicely with predetermined format in word?)
F)Be easily adjustable for the formatting whims of different schools?

I have so far had a lot of luck hiding table rows and columns via VBA,
but have gotten frustrated with things like disappearing cell borders
(oh, so A1 had a right border, but B1 didn't have a left border), the
irrational logic of: hiding columns + adjacent merged sections =
kablooey, the ease of adding new columns/rows with correct formatting
already in place, changing color schemes without individually selecting
different sections.

Wow. As Gramdpa Simpson said, "Anyway, long story short... is a phrase
whose origins are complicated and rambling..."

I realize I am asking about 20 different specific questions, but I'm
really not trying to get y'all to do my work for me. Mainly I'd love
tips on how to better understand my question, links to folks that have
discussed this before, the order I should consider tackling problems
("Do the borders last."), code snippets as a guide (I'm fairly code
savvy), grandiose yet vague theories...

Thanks,

Taylor Bryant

I have a Macro that populates a report by doing the following:

1) Copies a cell containing Procedures and pastes it into another worksheet ("Results"), in cell A, with the "Center across Selection" set for columns A:G, then autofits the height

2) Copies a range of data from another worksheet (columns A:W) and pastes it under Procedures

3) Copies a cell containing Solutions and pastes it into another worksheet ("Results"), in cell A, with the "Center across Selection" set for columns A:G, then autofits the height

4) Pastes a blank line after, then moves on to the next step.

The full code is at the bottom of this post. The problem is in pasting the data (A:W). For some reason my paste is wonky, and columns FGHI seem to be jumbled together on the report, with the side borders gone. I can manually fix this by selecting just the data for step2 on the report page and aligning it first Left (or Right) and then Center. I tried to record a macro to do that, and tried using it immediately after Step 2, but it's selecting the range on the other worksheet to adjust, not the range it just pasted. How do I get it to adjust just the data I just pasted?

My problem seems to be localized to this area, and I think it has to do with my Adjust Macro not selecting what I want it to select. Here's the specific bit of code:

 'Copy and paste only the
filtered data
           Range("J1").Activate
        Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)

         'Clear the error if there was one
          Err.Clear
         'Return error control back to the system
          On Error GoTo 0
          
' Adjust Macro
' Macro recorded 12/17/2009 by ME
    
        With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
and here's the full code:
Sub Step_1()

  Dim DstWkb As Workbook
  Dim Rng As Range
  Dim RngEnd As Range
  Dim cell As Range
  Set Rng = Selection
  
  Set DstWkb = Workbooks("NewDirection.xls")
  
  'Copy and paste the Procedure
    Sheets("Steps").Range("C2").Copy
    Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A:G").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .Font.Bold = True
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    'Autofit the heighth for Procedure
    Sheets("Results").Range("A65536").End(xlUp).Offset(0, 0).Rows.EntireRow.AutoFit
     
'Restrain the filter to cells from A1 to the last entry in column W
          With DstWkb.Worksheets("Data")
            Set Rng = .Range("A1:W1")
            Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
            Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, .Range(Rng, RngEnd))
          End With
         'Filter the data using column M
          Rng.EntireRow.Autofilter Field:=13, Criteria1:=("0")
          'Display a message if no valid criteria exists
          If Rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
          MsgBox "Step 1:no Zero FTEs were found"
                    
          End If
          'Trap the error if there were no matches
          On Error Resume Next

           'Copy and paste only the filtered data
           Range("J1").Activate
        Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)

         'Clear the error if there was one
          Err.Clear
         'Return error control back to the system
          On Error GoTo 0
          
' Adjust Macro
' Macro recorded 12/17/2009 by ME
    
        With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
          'Copy and paste the Solution
            Sheets("Steps").Range("D2").Copy
            Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A:G").Select
        Selection.Activate
        Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Font.Bold = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
     'Autofit the heighth for Solution
    Sheets("Results").Range("A65536").End(xlUp).Offset(0, 0).Rows.EntireRow.AutoFit
    
    'Make a blank row
    ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "Skip a line"
    With ActiveCell.Characters(Start:=1, Length:=11).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    
      'Turn off the autofilter
          Worksheets("Data").AutoFilterMode = False
      'Go to the next step
          'Run "Step_2"
End Sub


Hello,

I would like to know if it's possible to create a function to aggregate consecutive cells but only show the calculation once there is a blank cell. In other words:

I will manually input A1 = 100, B1 = 200, C1 = 100, D1 = blank, E1 = 100, F1 = 150, G1 = blank.

In row 2, I would like to use the "center across selection" formatting and create a function that will aggregate these cells and show these results:

A2:C2 (centered across) = 400, E2:F2 (centered across) = 250.

Is this possible? Does it make sense?

Thank you in advance for your help!

tinder

In search for a vertical equivalent of "center across selection" I tried "Justified" and "Distributed", but they did not do the job.What are they actually doing?Is there a vertical equivalent of "center across selection"?I know I can merge the cells and align vertically centered, but merged cells are a nuisance and I prefer not to use that solution.

NSV

I've been searching a while for a simple way to have default values for cells. Having default values allows the user to change data, but the sheet will revert back to the default values when the user input is deleted.

It's really (really!) simple:

Cell B1 is the cell that the user can change.
Cell A1 contains the default value ("Default")

Select both cells
Format cell: text alignment : horizontal formatting :choose "Center across selection"
Reduce column A width to 1 pixel...

This will cause the Default value to Appear in cell B1.

When you want to use the users input, Cell A1 formula could be:
=if(isblank(B1), "Default", B1)

Use the value in cell A1 for further calculations.