Solved

Help with Excel VBA

Posted on 2012-03-14
10
246 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

21 Experts available now in Live!

Get 1:1 Help Now