Solved

Help with Excel VBA

Posted on 2012-03-14
10
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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