Solved

Coding a Variable Range in one Column in Excel

Posted on 2003-11-05
4
406 Views
Last Modified: 2010-04-17
I'm trying to automate a function in Excel where a specific column receives the end result of concatenating LName,FName MI into an inserted column.   I receive an Excel file daily with the information where this function needs to be done.

My problem is that every day I don't receive the same amount of rows of names that need to be concatenated and I can't seem to figure out how to NOT hard code the Range and get it to work.  

In the code below you'll notice the Range is from rows B2 to B25.  The next day, I may only get 10 rows or the following day 50 rows, etc, etc.  

If I expand the Range out beyond the amount of rows I know I'll get (lets say B100), I end up with a column of "," (commas) after the concatenated names and then the other function I have beyond that (save as a CSV file), I end up with all the extra commas in the end that I don't want.   I couldn't seem to reverse engineer anything to get that working.

I was hoping not to have to go in and change the hard code every time I get a new file as I wanted to just have the user click the button and it will adjust to the amount of rows for that file for that day.


Is there code that will allow this to happen?  

I'm quite new at VB so if you can offer assistance, I'm in the "literal...need it spelled out" mode my learning curve is still high.  Any assistance would be greatly appreciated, as my multiple attempts have not worked.
TY...Shannon

This is the code I have thus far....
'Changes name format

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&"",""&RC[2]&"" ""&RC[3]"
    Selection.AutoFill Destination:=Range("B2:B25"), _Type:=xlFillDefault
0
Comment
Question by:DURRER
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
JohnChapin earned 50 total points
ID: 9687120
You are also getting an extra space that you cannot see.

Try this macro1 from your button:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/4/2003 by CHAPINJ
'
Dim i As Integer
Dim str As String

    For i = 1 To 18
        If Trim(Cells(i, 1).Value) <> "" Then
            str = Trim(Cells(i, 1).Value)
            If Trim(Cells(i, 2).Value) <> "" Then
                str = str & ", " & Trim(Cells(i, 2).Value)
                If Trim(Cells(i, 3).Value) <> "" Then
                    str = str & " " & Trim(Cells(i, 3).Value)
                End If
            End If
            Cells(i, 4).Value = str
        End If
       
    Next i
End Sub


and set the i range to guarantee that you will cover all the possible rows.
(You can test thenumber of filled rows ahead of time, but I see no need here.)

Need MOre. Let me know.
John
0
 
LVL 2

Expert Comment

by:wellilein
ID: 9687132
If all rows contain a value, you might use this:

    Dim row As Long
    row = 2
    While (Range("B" & row).FormulaR1C1 > "")
        row = row + 1
    Wend
    MsgBox ("number of rows:" & row - 1)

It stops if an empty cell was found.
0
 
LVL 2

Expert Comment

by:wellilein
ID: 9687207
Whole code would be

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
   
    Dim row As Long
    row = 2
    Do
        row = row + 1
    Loop While (Range("C" & row).FormulaR1C1 > "")
   
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&"",""&RC[2]&"" ""&RC[3]"
    Selection.AutoFill Destination:=Range("B2:B" & row - 1), Type:=xlFillDefault

   
0
 

Author Comment

by:DURRER
ID: 9687868
Thank you John for the code...it works great.  It even solved a second issue with the extra space for those that did not have a middle initial.  Many many thanks!!!

I tried Wellilein's code as well and I couldn't get it working YET...that is, I'm going back to try it again because I never know when I'll need code for certain situations...I thank you as well for posting.

Regards...Shannon
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

Suggested Solutions

Title # Comments Views Activity
word0 challenge 3 78
allswap challenge 6 79
creating threads in delphi 1 82
Please explain: Aspect Oriented Programming 2 85
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
A short article about problems I had with the new location API and permissions in Marshmallow
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

896 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

19 Experts available now in Live!

Get 1:1 Help Now