Improve company productivity with a Business Account.Sign Up

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

Help with Excel VBA

i have an excel file. How to :
1.  how to aligh left the first column, center the second column, the rest of columns should be right
2. how to make auto width for all cells
0
rfedorov
Asked:
rfedorov
  • 5
  • 4
2 Solutions
 
Anthony BerenguelCommented:
rfedorov,

try this...
Sub customFormatCurrentWorksheet()
    
    'right align all cells
    With Cells: .HorizontalAlignment = xlRight: End With
    
    'center align column B
    With Columns("B:B"): .HorizontalAlignment = xlCenter: End With
    
    'left align column A
    With Columns("A:A"): .HorizontalAlignment = xlLeft: End With
    
    'auto width for all cells
    Cells.EntireColumn.AutoFit
    
End Sub

Open in new window

0
 
armchair_scouseCommented:
Easiest way to work this out and to start learning VBA (other than going on a coding course) is to record a macro and then examine what it does.

a) Go to the Developer tab in Excel, in the Code section, select Record Macro, change the 'Store Macro In' to say 'This Workbook' and click OK.  
b) Format the columns as you wanted
c) Go to Go to the Developer tab in Excel, in the Code section, select Stop Recording.
d) Press Alt+F11 to access the VBA Editor window.
e) In the Project window (the treeview to the left of the screen), you should see something called 'VBAProject'.  There is a node/branch called 'Modules'.  If you click on it, you should see 'Module1' appear.  Double-click on Module1.
f) You might see something like this (N.B. I have stripped out a lot of the bits that you don't need):

Sub Macro1()
'
' Macro1 Macro
'
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
    Columns("B:B").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("C:E").Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Columns("A:E").AutoFit
End Sub

Open in new window


A lot of gobbledygook until you understand it, I know!! Hope this helps and good luck with the VBA coding :o)
0
 
rfedorovAuthor Commented:
Thank you so much, got the idea
last question
how to add the border around the area, i want the whole area, and numbers of rows always is different
when i record a macro it put the border around the existing data...
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony BerenguelCommented:
rfedorov,

I would suggest implementing conditional formatting in your worksheet so that if the cell is not blank then it gets borders.

Let me know if you have questions.
0
 
rfedorovAuthor Commented:
no, there is no a chance that the cells will be  blank
0
 
Anthony BerenguelCommented:
Are you saying your worksheet has no blank cells? If so, then I'm confused about your statement,
 "i want the whole area, and numbers of rows always is different
when i record a macro it put the border around the existing data..."


What is the existing data? And if there is not existing data, wouldn't that mean some cells are blank?

However, if its true that there are no blank cells anywhere on your worksheet, I think the best solution is still implementing conditional formatting on your worksheet. The condition does have to be if the cell is blank, it could be whatever you want it to be in order to get borders around your cells.
0
 
rfedorovAuthor Commented:
it could be 12 row of data, it could be 2 rows of data
0
 
Anthony BerenguelCommented:
That's what I mean, if there are two rows of data, then there are many, many blank cells in the remaining rows. However, I'm getting that you dont want to use conditional formatting so I'm playing with my solution to see if I can incorporate your needs.
0
 
rfedorovAuthor Commented:
ok,
let say i have a Range

Range("A3:C20").Select

'''
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


this is working but i want to make excel understand, that it should select all data in the sheet and put the border around it....
0
 
Anthony BerenguelCommented:
try this...
Sub customFormatCurrentWorksheet()
    
    'right align all cells
    With Cells: .HorizontalAlignment = xlRight: End With
    
    'center align column B
    With Columns("B:B"): .HorizontalAlignment = xlCenter: End With
    
    'left align column A
    With Columns("A:A"): .HorizontalAlignment = xlLeft: End With
    
    'auto width for all cells
    Cells.EntireColumn.AutoFit
    
    'add border to data in sheet
    Call addBorderToAllUsedCells
End Sub
Sub addBorderToAllUsedCells()
    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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now