+ Reply to Thread
Results 1 to 2 of 2

referring to workbook

  1. #1
    Fabrizio
    Guest

    referring to workbook

    I have a sub that searches for blanks and removes them. however someone
    helped me with the code and i do not know how to write it so that it works
    for any workbook. In the code below a specific workbook is used but if I save
    the file under different name it does not work. how do you write the code so
    that it refers to the current workbook that is open?


    Public Sub findAndRemoveBlanks()
    Dim WB As Workbook
    Dim SH As Worksheet
    Dim rng, rCell As Range

    Set WB = Workbooks("20060619c")
    Set SH = WB.Sheets("Indata")
    Set rng = SH.UsedRange
    'Set rng = SH.Range("A1:p100")
    For Each rCell In rng.Cells
    With rCell
    If Not IsEmpty(.Value) Then
    If Not UCase(.Value) Like "*[A-Z]*" Then
    .Replace What:=" ", Replacement:=""
    End If
    End If
    End With
    Next rCell

    End Sub

  2. #2
    comparini3000
    Guest

    RE: referring to workbook

    i forgot to add this to my other post, but since the workbook and sheet was
    not specified, excel will assume it's the current workbook and current sheet,
    meaning when you run the macro, it's only going to affect the selected sheet.

    also, change the line
    dim rng, rcell as range to
    dm rng as range, rcell as range

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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