# How to find the number of characters in a cell of specific column (for example column 8)

1. ## How to find the number of characters in a cell of specific column (for example column 8)

How to find the number of characters in a cell of specific column (for example column 8)

Lets say I have Cells filled up with some characters in column 8

Column 8
AA
BB

CC
IIIII
EEEEEE

Result should be ( assume Column 8 is the last column)
Column 9
A
A
A
I
E

I want to write a macro that finds the number of characters in a cell and if that number is less than 3 then assign "A" to the last active column +1
If number of characters are greater and equal to 5 then write 4 th character in to the last active column+1
If the number of characters =0(Empty) Then delete the whole row.

I am new to VBA so I don't know how some syntax work like the Len(), but the Idea is this:

Option Explicit
Sub Check()
Dim myRange As Range, sRange, Sheet1 As String
Dim I, ROWNUM, COLNUM As Long
Dim ws1 As Worksheet

ROWNUM = Worksheets(sheet1).Cells(Rows.Count, 1).End(xlUp).Row
COLNUM =Worksheets(sheet1).Cells(1, Columns.count).End(xlToLeft).Column
Set ws1=Worksheets(Sheet1)

For i=1 To ROWNUM
If Len(8)<3 Then
ws1.Cells( i,COLNUM +1)="A"
Else If Len(8) >=5 Then
ws1.Cells( i,COLNUM+1)=Mid(ws1.Cells( i,8),4,1)
Else If Len(8)=0 Then
Rows( i).Delete
End if

Next i

End Sub

Thanks

2. ## Re: How to find the number of characters in a cell of specific column (for example column

You were on the right track. The thing is, if you're looping down through the rows, if you delete a row then you don't want to increment the row number (deleting a row, in effect, already takes care of that) and it looks like your code was doing just that.

Thus, rather than use a FOR NEXT loop, I changed it to a DO UNTIL loop.

This code seems to work with all of the data I tested it with. Modify as needed.

``Please Login or Register  to view this content.``
Edited. Found a small bug.

Please confirm you're not doing anything when the length is exactly four.

3. ## Re: How to find the number of characters in a cell of specific column (for example column

Dim last_row As Integer, dont_increment As Boolean, deletes As Integer?

By the way what does Range("H65536") mean?

4. ## Re: How to find the number of characters in a cell of specific column (for example column

No, "last_row" ideally, should be dimensioned as Long, since in theory you might have more than 32,767 rows of data.

Yes, "don't increment" is just a switch that will either be 1 or 0. You can dimension this as an Integer or as Boolean.

"Deletes" keeps track of the number of rows deleted. Since this, in theory, might also be more than 32,767 in number, this should also be dimensioned as Long.

last_row = Range("H65536").End(xlUp).Row is just one of the many ways of finding which of your rows is the last one to have data. In this case, the H is for the H column. I believe the statement searches the H row, from bottom to top, starting with row 65536, I guess. (That number should probably be changed... I just grabbed that code somewhere, when I was looking for a method to determine the last row of data.)

I hope this helps.

5. ## Re: How to find the number of characters in a cell of specific column (for example column

Note that if you don't dimension variables, the default type is "variant" which often will be exactly what you need anyway.

http://office.microsoft.com/en-us/ex...080556594.aspx

(Strings need to be dimensioned as strings, however.)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1