Solved

Help with Excel VBA

Posted on 2012-03-14
10
243 Views
Last Modified: 2012-06-21
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
Comment
Question by:rfedorov
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37721436
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
 
LVL 9

Assisted Solution

by:armchair_scouse
armchair_scouse earned 250 total points
ID: 37721457
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
 

Author Comment

by:rfedorov
ID: 37721761
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
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37721788
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
 

Author Comment

by:rfedorov
ID: 37722104
no, there is no a chance that the cells will be  blank
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37722174
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
 

Author Comment

by:rfedorov
ID: 37722181
it could be 12 row of data, it could be 2 rows of data
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 37722208
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
 

Author Comment

by:rfedorov
ID: 37722222
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
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 250 total points
ID: 37722254
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now