1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Selecting a range using a macro

Discussion in 'Business Applications' started by PincivMa, Feb 2, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Again

    This is probably one of the easiest macros to solve. I have data in Column A but the data is not in every cell. I am using a macro to delete the last character from the right. The macro works fine but it stops at an empty cell. It does not continue down the list. I have attached the spread sheet. Run the macro that I have created and you will see what I mean.

    Thanks

    Mario
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try this

    Code:
        Range("A1").Select
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        If cell.Value <> "" Then
        cell.Value = Left(cell.Value, Len(cell.Value) - 1)
        End If
        Next cell
    Rollin
     
  3. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi Rollin_Again

    Thanks a million. The macro works great.

    Mario
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No need to Select any cells here.

    Also, if you have a large list, looping may not be the best idea. You could insert a column, use a formula to get the data, then leave the values as static and delete the original column. Of course this will not work very well if you have dependent calculations based on the data in the original column. Here is an example (assuming your data is in column A and run on the activesheet)...

    Code:
    Dim LastRow as long
    lastrow = cells(rows.count, "A").end(xlup).row
    application.displayalerts = false
    columns(1).insert
    with range("A1:A" & lastrow)
        .formula = "=IF(LEN(B1)>1,LEFT(B1,LEN(B1)-1),B1)"
        .value = .value
        .offset(0, 1).entirecolumn.delete
    end with
    application.displayalerts = true
    Looping definitely has it's purposes and can be a great deal of help. However, we always need to look at improving efficiency and maintaining good clean code that does the job to the best of its ability. So I don't want to take anything away from loops (I use them frequently), but like everything else, use in moderation. :)

    Edit: Also check out John Walkenbach's PUP add-in (does cost a small fee, but well worth it) or ASAP Utilities (a free download) as both of these have this functionality built into it.

    HTH
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/540556

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice