Free Microsoft Excel 2013 Quick Reference

Using Code to Adjust Specific Column Width

I have a spreadsheet that retrieves data after it has
retreived the data I want to use code to look in row 5 and
go thru each cell up to the last colum, if there is
nothing in the cell I want it to resize it to a smaller
size,

is there anyway of doing this

Thanks


Post your answer or comment

comments powered by Disqus
Hi,

I have a workbook say A.xls, which contains few hidden cells. I copy only visible cells from A.xls to B.xls and save it under the same location as A.xls. I wrote a code to do all this but now my problem is.... after copying only visible cells from A.xls to B.xls I am not getting correct column width in B.xls. I need to do some exta editing in B.xls to get the same column width as in A.xls. I can get same format of the cell if there are no hidden cells in A.xls, but as I filter few cells in A.xls and copy to B.xls then I need to do some editing in B.xls to get the same format as in A.xls. Here is the code I wrote to get the work done. Hope to get some modification to get the appropriate result. Thank you.


	VB:
	
 BOM Generator() 
    Application.ScreenUpdating = False 
     
    Dim tBook, sBook As Workbook 
    Application.ScreenUpdating = False 
    Set sBook = ThisWorkbook 
    Set tBook = Workbooks.Add 
    tBook.SaveAs ThisWorkbook.Path & "A.xls.xls" 
    sBook.Activate 
    Sheets("BOM").Select 
    Cells.SpecialCells(xlCellTypeVisible).Copy Workbooks(tBook.Name).Sheets(1).Range("A1") 
    tBook.Save 
    Set sBook = Nothing 
    Set tBook = Nothing 
    Application.ScreenUpdating = True 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Sir,

I am attaching my excel spreadsheet which contains a document. Due to different sizes of data the columns widths are seems awkwardly. Kindly suggest an idea to adjust the column widths according to their data sizes. While doing the same the upper or lower part of the document is getting disturbed.

Hi,

I have a worksheet with several tables, and each of the tables is a named range (file attached, the ranges are, from top to bottom, "Breakfast", "Lunch", "LateLunch", "Dinner" and "Night".

I already know I can use the following code to count the number of rows in a range:


	VB:
	
RowsCount = Sheets("Tables").Range("Lunch").Rows.Count 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above would return RowsCount = 16.

However, I need to count the used rows of a specific column within the named range. For example, say I wanted to count the number of used rows in column D of the "Lunch" range (15, in this case).

Is there simple VBA code for this?

Many thanks

Joe

I have a summary sheet that has a vlookup that pulls back data from another sheet. The formula works very well however when I use my drop down to change the value the vlookup it pulls back data of different length, so every I have to adjust the column width of my summary sheet. Can some one please help me with a marco that adjusts the width of columns. I could use the macro recorder but when I do it sets itself for the length of the current summary and I need something that can fluctuate with the different lengths.

Thank you in advance.

Jason

Hi,

I am would like to write some code to set the column width of all selected
worksheets to match the column widths in a specified sheet. Does anyone know
of any existing code or an add-in (free) that does this?

I often use multipage workbooks and can easily set the columns on all sheets
to be the same on all sheets by selecting all sheets and changing the column
width. Problems arise when the columns are later changed and I find myself
forever resetting the column widths.

Any assistance would be appreciated.

Regards.

Sean

Hi there. I have a workbook with 17 sheets that imports data each month. After import, I'd like to adjust several columns in a sheet. Right now, my macro is very manual (shown below). It continues with the same code on all sheets. But isn't there an easier way to attack this?


	VB:
	
Sheets("Trials").Select 
Cells.Select 
With Selection.Font 
    .Name = "Arial" 
    .Size = 8 
    Columns("H:H").Select 
    Selection.ColumnWidth = 40 
    Columns("I:I").Select 
    Selection.ColumnWidth = 10 
    Selection.NumberFormat = "#,##0.00" 
    Columns("J:J").Select 
    Selection.ColumnWidth = 10 
    Selection.NumberFormat = "#,##0.00" 
     
End With 
 
Range("A2:A1000").Select 
With Selection 
    .HorizontalAlignment = xlGeneral 
     
    Range("B2:B1000").Select 
    With Selection 
        .HorizontalAlignment = xlGeneral 
         
        Range("C2:C1000").Select 
        With Selection 
            .HorizontalAlignment = xlGeneral 
             
            etc... 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a workbook that contains quite a bit of data. The main worksheet performs some calculations on the other worksheet data and gives me either a number of 1 to 200, however there will be numerous columns with no results (blank cells). I'm trying to figure out how to use VBA to autofit the column width so that the columns with no data will have a width of ZERO, while the columns with data will have a width of 4.

Currently I have this in VBA:


	VB:
	
 ColumnWidth() 
    Cells.Select 
    Range("F6").Activate 
    Cells.EntireColumn.Autofit 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, it doesn't reduce a column with no data to ZERO. Can anyone help?

Hello

This could be a fairly simple yes or no, I am hoping that I can use code to select sections of my spreadsheet and save it based on the contents of the cells. For example, I have a long list of account codes, and a set of managers who have codes assigned to them. Some have 1 code, some have 25 codes. I have used a lookup to pull the managers name into the last column of my spreadsheet based on the account code in the first column. I'd now like to have a process I can run which picks up all occurrences of a managers name, then saves everything in the rows related to it into a new spreadsheet. This may be impossible......

The other alternative is to manually go through looking for the names and copying, pasting and saving, which is taking 2 days at the moment. So any advice would help me out!

Thanks

I am attempting to duplicate a template spreadsheet using a macro. However when I program the column width using a macro they are different and I am not sure what is happening.

Here is an image of the original file

The column widths are as follows
A=.9
B=14
C=16
D=12
E=10

Here is the page that my macro generates

I am using the following code to set the column widths
Code:
Columns("A:A").ColumnWidth = 0.9
    Columns("b:b").ColumnWidth = 14
    Columns("c:c").ColumnWidth = 16
    Columns("d:d").ColumnWidth = 12
    Columns("e:e").ColumnWidth = 10
When I hold the mouse over the seperator between the columns they are both saying that they are the same size but they are physically different.

Before anybody states the obvious. The magnifications are both the same at 100%.

Anybody have any idea as to why this is happening?

Ok, on my excel sheet i have checkboxes that insert text in the cells when checked. Problem is for the text that have 4-5 lines, I can not write the code to adjust the column height when inserting. I tried to use FORMAT-->ROW/COLUMN-->AUTOFIT but it doesnt work and i was told its because i have merged cells.
Please let me know if you know the solution to this. The following is the code i use for checkboxes.

Thanks!

Private Sub CheckBox1_Click()
If CheckBox1 = True Then

Dim NextCell As Range
'Set NextCell to last unavailable cell beyond D4 (next cell is available)
Set NextCell = Sheet1.Range("B41").End(xlDown)
'Quit if no place to put value (next available cell is beyond row 20)
If NextCell.Row < 32000 And NextCell.Row > 71 Then Exit Sub
If NextCell.Row > 32000 Then Set NextCell = Range("B41")

NextCell.Offset(1).Value = "Our bid is based on two (2) mobilizations to complete all work. Additional mobilizations requested by the contractor to complete all work, an additional fee of .50 cents per L.F. of total footage of project shall be charged per each mobilization"

End If

End Sub

With help from this group, I am using the following code to hide empty
columns in my spreadsheet. It works when my sheet is not protected,
however, when I deploy this sheet for use by others it will need to be
password-protected. What can I add to the code to unprotect the sheet
prior to hiding the columns and then re-protect the sheet after the
columns have been hidden? Will my password-protection be preserved?
Please help...

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82

Application.ScreenUpdating = False
With Sheets("Box")
Dim col As Range
For Each col In .Range("C10:AF82").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0

Next
End With
Application.ScreenUpdating = True
End Sub

Is there a way to adjust the column width automatically to fit the length of
the longest data in the cell. I know how to do it one column at a time but
not how to do all columns of data at once.

Thanks,

Jerry

I'm trying to use code to force the entire column to be currency. I also
have another column I'm trying to force to text, any help is appreciated

Hi,

Programmatically I try to initialize the column width from a pixel
value I have. I found a conversion function but doesn't function quite
accurate. In below code, GetScreenXPI returns 96 on my computer. My
default width is 8.43 char (64 pixel). PioxToCharX in this case would
return 8 char for 64 pixels. What am I missing? Any help would be
appreciated.

Reto
www.xcellery.com

' Conversion code
'*********************

Public Const TWIPS_PER_INCH = 1440 ' number TWIP units per
inch
Public Const TWIPS_PER_CHAR_X = 120 ' number TWIP units per
logical character

Public Function PixToCharX(ByVal pixels As Long) As Long
PixToCharX = ((pixels / GetScreenXDPI) * TWIPS_PER_INCH) /
TWIPS_PER_CHAR_X
End Function

Function GetScreenXDPI() As Long
Dim hdc As Long
hdc = CreateICA("DISPLAY", vbNullString, vbNullString, 0)
If (hdc <> 0) Then
GetScreenXDPI = GetDeviceCaps(hdc, 88) 'screen res x
DeleteDC (hdc)
End If
End Function

I'm trying to use a listbox in a form and set the columns up so that they are the same widths as the sheet, so nothing gets truncated in the form. On the sheet, they are set up to Autofit. I have added code to get the column widths for each column on the sheet. The column widths on the sheet are 10, 39, 13, and 10. I am then trying to bring those columns of data from the sheet into the listbox. When I set the .ColumnWidths for the form equal to 10;39;13;10, it does not display correctly in the form. The columns are way to small. Is there a way to do this to base it off the column width on the sheet so nothing gets truncated?

I know nothing in maacro and codes but interested to use someones sweat,

i got a macro from one of the groups and i use for extracting data from many excel workbooks containing only one worksheet, (inspection request with typical data). here, to extract data required manual intervention to open the folder and selecting files. i need macro to select those files from a given folder without any manual intervention. if someone

thanks in advance

Sub Summary_cells_from_Different_Workbooks_2()
'This example use the function LastRow
Dim FileNameXls As Variant
Dim SummWks As Worksheet
Dim ColNum As Integer
Dim myCell As Range, Rng As Range, fndFileName As Range
Dim RwNum As Long, FNum As Long, FinalSlash As Long
Dim ShName As String, PathStr As String
Dim SheetCheck As String, JustFileName As String
Dim JustFolder As String

ShName = "inspection request" '<---- Change
Set Rng = Range("B3:B46") '<---- Change

'Select the files with GetOpenFilename
FileNameXls = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)

If IsArray(FileNameXls) = False Then
'do nothing
Else
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Use this sheet for the Summary
Set SummWks = Sheets("log") '<---- Change

For FNum = LBound(FileNameXls) To UBound(FileNameXls)
ColNum = 1
RwNum = LastRow(SummWks) + 1
FinalSlash = InStrRev(FileNameXls(FNum), "")
JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

'If the workbook name already exist the row color will be Blue
Set fndFileName = Nothing
Set fndFileName = SummWks.Cells.Find(JustFileName)
If Not fndFileName Is Nothing Then
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
.Interior.Color = vbGreen
Else
'Do nothing
End If

'copy the workbook name in column A
SummWks.Cells(RwNum, 1).Value = JustFileName

'build the formula string
JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''")
PathStr = "'" & JustFolder & "[" & JustFileName & "]" _
& ShName & "'!"

On Error Resume Next
SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1") _
.Address(, , xlR1C1))
If Err.Number <> 0 Then
'If the sheet name not exist the row color will be Yellow.
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
.Interior.Color = vbYellow
Else
'Insert the formulas
For Each myCell In Rng.Cells
ColNum = ColNum + 1
SummWks.Cells(RwNum, ColNum).Formula = "=" _
& PathStr & myCell.Address
Next myCell
End If
On Error GoTo 0
Next FNum

' Use AutoFit to set the column width
SummWks.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

I'm using the following code to enter a ComboBox entry from a TextBox:

If TextBox1 > "" Then UserForm2.ComboBox1.AddItem TextBox1.Text: UserForm2.Show

How can I assure that the entries are in alphabetical order?

Also, I'm using code to create new worksheets, how can I assure they're in alphabetical order too?
Sheets.Add.Name = TextBox1 & " " & TextBox2

Is there anything I can do to display a message to users if data is input to
a specific column in a spreadsheet, like a pop up window?

Somthing like one of these may work...

a.
Worksheets("Sheet1").Range("A18"). _
AutoFormat Format:=xlRangeAutoFormatClassic1

b.
Columns("E:E").EntireColumn.AutoFit

You will have to play with these, and the Range/Columns as
required.

>-----Original Message-----
>VBA code changes the Excel column/row size, Can we write
>VBA code to set all column/row's size back to default
size?
>
>Thanks in advance!
>.
>

Hi plz help, the Ruler across the top that allows you to adjust the column widths (A B C D etc). Is it possible to insert another Ruler, further down the same page, to allow you to adjust the column width differently. if so how. On Excel 2007

I'm trying to have separate column widths in the same column, based on the rows.

the top half of my spreadsheet are assumptions, formatted with the corrects widths needed, but then below I have calculations and such that I do not want the same widths for. how can I lock in the top part so I can decrease the column widths for the bottom half of my spreadsheet?

hope this makes sense.

As I move down my speadsheet, I wish to change the column width for all data
below a certain row. How do I do this?

Hi.

I need to create VBA code to hide empty columns. Problem is when column considered empty it always has header row(s). I guess code should have defined Range of rows and columns such as A2:AZ50. In this case row is header. Thanks for your help.

Hi,

I have an Excel 2003 worksheet that has several columns in it. I am working between columns A through AG with every other column being blank. I have the print adjusted to fit 1 page wide (Adjust To: 43%) in landscape to get it all to fit. I do have one print button that works great for printing a detailed report of my inventory worksheet but I am the only one who needs this info. I created another button that hides unwanted columns, prints, then unhides the colums. The problem I am having is that it still printing at scaled 43%. I need it to print full page width but the code I am trying does not work.

Here is the code as it is currently,


	VB:
	
 Picture150_Click() 
    Dim LastRow As Long 
     
    With ActiveSheet 
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        .PageSetup.PrintArea = "A1:AG" & LastRow 
    End With 
    ActiveSheet.Columns("I:L").EntireColumn.Hidden = True 
    ActiveSheet.Columns("Q:V").EntireColumn.Hidden = True 
    ActiveSheet.Columns("Y:AF").EntireColumn.Hidden = True 
     
    With ActiveSheet.PageSetup 
        .CenterHorizontally = True 
        .Orientation = xlLandscape 
        .FitToPagesWide = 1 
    End With 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
     
    ActiveSheet.Columns("I:L").EntireColumn.Hidden = False 
    ActiveSheet.Columns("Q:V").EntireColumn.Hidden = False 
    ActiveSheet.Columns("Y:AF").EntireColumn.Hidden = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am not getting an error with this code, it just isn't fitting to the page width. My eyes are still good so I can read it but my bosses eyes are not.


No luck finding an answer? You could always try Google.