Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

Get Column Width Excel VBA

Hello:

I have an excel sheet which contains multiple columns (provided by a vendor).

Each column should contain data of certain length. eg. Column A can hold data for MAX 20 characters, column B can hold data for MAX 40 characters etc.

I need to develop a utility which will read the MaxSize, parse the data and display a short report on the findings:

Sample Report:
Column	MaxSize	Report
A	20	There are 55 rows in column A with more than 20 characters
B	50	No errors
C	10	No Errors
D	15	There are 2 rows in column D with more than 15 characters

Open in new window


Thank you.
0
nainil
Asked:
nainil
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
hitsdoshi1Commented:
You can just write and function to check or just write vba

to check the cell length formula is Len(A1) and you can loop thru to check whole column
to extract just 20 characters from a string you can write something like this =Left(A1,20)

0
 
nainilAuthor Commented:
Thank you hitsdoshi1: Yes, Len function is what I used to do till now, manually. However, I would like to see some VBA code which would parse through the Column range and provide some feedback to the end user?
0
 
krishnakrkcCommented:
Hi,

Try something like this

=SUMPRODUCT(--(LEN(A2:A100)>20))

This will give you the count of rows where cells having more than 20 characters in A2:A100.

Adjust the range and max len.

Kris
0
Industry Leaders: 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!

 
hitsdoshi1Commented:
There you go....this code checks length of column "A"  and if column "A" is blank (if length is 0 then its stops)


Sub checkcol()
m = 1
mylist = ""
For m = 1 To 65000
    If Len(ActiveSheet.Range("A" & m)) = 0 Then
        Exit For
    End If
    If Len(ActiveSheet.Range("A" & m)) > 20 Then
        mylist = mylist & "Cell A" & m & " is " & Len(ActiveSheet.Range("A" & m)) & " Characters long..." & Chr(13)
    End If
Next
MsgBox mylist
End Sub

Open in new window

0
 
nainilAuthor Commented:
Thank you hitsdoshi1:

It works. However, when I am trying to save the information in a cell (of what is returned), i see a special character Like the one attached.

Any idea how to remove the same?
test-excel.jpg
0
 
hitsdoshi1Commented:
There you go......this will write on column "D" for which len is > 20
Sub checkcol()
m = 1
mylist = ""
For m = 1 To 65000
    If Len(ActiveSheet.Range("A" & m)) = 0 Then
        Exit For
    End If
    If Len(ActiveSheet.Range("A" & m)) > 20 Then
        ActiveSheet.Range("D" & m) = "Cell A" & m & " is " & Len(ActiveSheet.Range("A" & m)) & " Characters long..."
    End If
Next
MsgBox mylist
End Sub

Open in new window

0
 
nainilAuthor Commented:
I was trying to put the information on 1 cell... is that why we are seeing the special character?
0
 
hitsdoshi1Commented:
No its cos I am using Carriage Return (Chr(13)) to separate each line. Try using "|" character instead of chr(13)
Sub checkcol()
m = 1
mylist = ""
For m = 1 To 65000
    If Len(ActiveSheet.Range("A" & m)) = 0 Then
        Exit For
    End If
    If Len(ActiveSheet.Range("A" & m)) > 20 Then
        mylist = mylist & "Cell A" & m & " is " & Len(ActiveSheet.Range("A" & m)) & " Characters long..." & "|"
    End If
Next
MsgBox mylist
End Sub

Open in new window

0
 
nainilAuthor Commented:
That does not help... it simply appends information separated by a PIPE.
0
 
broro183Commented:
hi,

Here is a slight modification to krishnakrkc's SumProduct solution which you can copy down as necessary:
in cell Summary!$D2 type
=SUMPRODUCT(--(LEN(INDEX(Data!$A$2:$D$10,,MATCH(Summary!$B2,Data!$A$1:$D$1,0)))>Summary!$C2))

Open in new window


where:
Data!$A$2:$D$10 is the data range (including the headers) that you want to check.
Summary!$B2 contains the same value as the column header on the data sheet
Data!$A$1:$D$1 is only the range containing the headers on the data sheet
Summary!$C2 is the max allowed length

Once you have copied it down, you can create another column which contains an If statement to provide the text phrase for example:
in cell E2 type...
=IF(Summary!$D2>0,"there are " & Summary!$D2 & " rows in the '" & Summary!$B2 & "' column, with more than "& Summary!$D2 &" characters.", "No Errors.")

Open in new window


The reason I have placed the above text result in a separate column is to minimise the number of times the required value being calculated, ie if it is in cell D2 then it only needs to be calculated once (per sentence).


hitsdoshi1,
To improve the speed of your suggestion, I recommend limiting the number of loop iterations, for example, you could replace this line...
For m = 1 To 65000
'with the line
For m = 1 To lastcell(activesheet).row
'if you include the below function with the existing code...
Function LastCell(ws As Worksheet) As Range
' sourced from http://www.beyondtechnology.com/geeks012.shtml
'Obj: to identify the lastcell on a worksheet (& not necessarily the active sheet)
Dim LastRow As Long
Dim LastCol As Long
    ' Error-handling is here in case there is not any
    ' data in the worksheet
    On Error Resume Next
    With ws
        ' Find the last real populated row
        LastRow = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByRows).Row
        ' Find the last real populated column
        LastCol = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByColumns).Column
        ' Finally, initialize a Range object variable for
        ' the last populated row.
        Set LastCell = .Cells(LastRow, LastCol)
        If LastCell Is Nothing Then Set LastCell = .Cells(1, 1)
    End With
    On Error GoTo 0
End Function

'to improve the speed even more you could turn off screenupdating & calculation, for example at the start of the code you could state
    Call ToggleRefreshApp(False)
''then have your other code...
    Call ToggleRefreshApp(True)

'as long as you include the below code too

Public glb_origCalculationMode As Long 'include this line at the top of the module
Sub ToggleRefreshApp(RefreshAppSettings As Boolean)
    With Application
        If RefreshAppSettings Then
            glb_origCalculationMode = .Calculation
        End If
        .EnableEvents = RefreshAppSettings
        On Error Resume Next
        '        .Calculation = IIf(RefreshAppSettings, glb_origCalculationMode, xlCalculationManual)
        .Calculation = IIf(RefreshAppSettings, xlCalculationAutomatic, xlCalculationManual)
        On Error GoTo 0
        .StatusBar = False    'this should really be stored as a glb variable & restored, but impact in this file is minimal
        .ScreenUpdating = RefreshAppSettings
    End With
End Sub

Open in new window


hth
Rob
0
 
broro183Commented:
Hi Nainil,

If you like the Sumproduct but would like an example file to be uploaded, let me know & I'll post one.

Rob
0
 
nainilAuthor Commented:
broro183:
Currently, solution provided by hitsdoshi1 is able to help me.

I do not mind looking into the second option... An example will definitely be appreciated.
0
 
hitsdoshi1Commented:
There you go....this will put the output in Cell D1.
Sub checkcol()
m = 1
mylist = ""
For m = 1 To 65000
    If Len(ActiveSheet.Range("A" & m)) = 0 Then
        Exit For
    End If
    If Len(ActiveSheet.Range("A" & m)) > 20 Then
        mylist = mylist & "Cell A" & m & " is " & Len(ActiveSheet.Range("A" & m)) & " Characters long..." & vbLf
    End If
Next
Range("D1") = mylist
End Sub

Open in new window

0
 
broro183Commented:
hi Nainil,

Please see the attached file for a SumProduct based solution as per my previous post which is based on Kris' initial suggestion.

This example is more of a summary in line with your initial post, than hitsdoshi1's latest code which provides row by row detail. Also, I recognise it's only an example too, but the vba suggestion could potentially be sped-up drastically by "limiting the number of loop iterations" to the used range (rather than 65k.


hth
Rob E-E-sumproduct-example.xls
0
 
nainilAuthor Commented:
Two excellent suggestions posted here. I will be working with both these options...
0
 
broro183Commented:
Great, I'm pleased we could help :-)

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now