?
Solved

Help with Excel VBA

Posted on 2012-03-14
10
Medium Priority
?
256 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 1000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

800 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