Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Excel macro help

I have an excel file with two sheets:
Contents of the first sheet:
Column 1      Column 2      Column 3      Column 4
a      45      33      5423
d      66      66      4
f      2      2      1
g      2      2      3
s      33      3      2
Contents of the second sheet:
Column 1      Column 2      Column 3      Column 4
a      28      33      5423
d      66      41      2
f      2      2      1
g      22      2      2
s      33      1      2
I would lke to get help how to:
1. put a border on those tables
2. Sort them by Column1
3. Allighn them left
4. make a word"Total" and actual number of rows (count) right under the table
FileToPresent.xls
0
rfedorov
Asked:
rfedorov
  • 6
  • 3
2 Solutions
 
armchair_scouseCommented:
Presuming you are using Excel 2010:

1.  Putting a border on a range of cells:  select range of cells, right-click and select Format Cells, select the Border tab, and then choose the Border you need (Outline for example).
2. Sort cells by Column 1:  select range of cells, select the Data tab, click Sort, in the Sort by column, choose Column 1.
3. Align cells left: right-click and select Format Cells, select Alignment tab, align as required.
4. Total: select cell immediately under table, e.g. in Column 1.  Type in the following formula: '=COUNT('  and then click on the first cell and drag down until all of the cells in that column of your table are selected, then type ')'.  You should ahve a completed COUTN formula, e.g. =COUNT(A1:A5)  if the cell range you selected was from cell A1 to cell A5.
0
 
BenefordCommented:
Formatting: Excel does a nice job with the Home tab's Format as a Table
  Select the table and click on the button and select the options you want.
  For more detailed control, you can right click and select Format Cells and use the Borders tab.

Sort: Select the table and on the Home tab, select Sort

Align: select the cells you want to align and click the Align Left button

Count: on the cell under the column you want to count, click on the arrow next to 'Autosum' and select 'Count numbers'
To get 'Total before the number, right click on the cell(s) and select Format Cells, select Custom number and overtype the current format with 'Total #'.

It's probably easiest to format after putting the totals in place.
FileToPresent.xls
0
 
rfedorovAuthor Commented:
Thank you guys, but that is not what i want...
I know how to do that, i asked the help with macro
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BenefordCommented:
Sorry. You didn't say 'macro' in the text.

The easiest way to see how to do this is to go to the developer tab and Start Recording.
Then do whatever you want, Stop recording and go and look at the macro that was created.

You'll have to tweak things a bit, but that's the best way to learn how-to with Excel macros.
0
 
rfedorovAuthor Commented:
-------------------> Excel macro help
this is my header :)
0
 
rfedorovAuthor Commented:
I know to use Recording, but every time the number of the sheets is different and numbers of rows different  too
0
 
rfedorovAuthor Commented:
Sub Macro1()
    Range("A1:D6").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "=COUNT(R[-6]C:R[-2]C)"
    Range("D9").Select
End Sub
0
 
BenefordCommented:
Here's a macro that iterates through the sheets and looks at row1/col1 to find the extent.
Combine that with your formatting above and you should be there.

Public Sub FormatSheets()
    Dim WS As Worksheet
    For i = 1 To ActiveWorkbook.Sheets.Count
        Set WS = ActiveWorkbook.Sheets(i)
        w = 1
        While WS.Cells(1, w) <> ""
            w = w + 1
        Wend
        h = 1
        While WS.Cells(h, 1) <> ""
            h = h + 1
        Wend
        WS.Cells(h, w - 1) = "Total " & h
    Next i
End Sub

Open in new window


Included in the attached.
FileToPresent.xls
0
 
rfedorovAuthor Commented:
Ok, i solved the problem with numbers of sheets and the border around all tables  :)))


Public Sub C_SelectAll()
Dim ws As Worksheet
Dim wb As Workbook
Dim x As Integer
Set wb = ActiveWorkbook
    For x = 1 To wb.Worksheets.Count
        wb.Worksheets(x).Select
        Call PutTheBorder
    Next x
End Sub
Sub PutTheBorder()
   
   Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

how to go to the end of the table and put the total count of records
0
 
rfedorovAuthor Commented:
i want the word total in cell A  and the count of the rows in cell B
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now