dynamic VB? - Page 2
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: dynamic VB?

  1. #11
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Not sure if my answer is pertinent, I assumed AA is a column versus a row. And I assume, Bob, that you're shifting columns versus rows, based on your references...

    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-04-04 18:38 ]

  2. #12
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may need to streamline this a little:
    Code:
    Dim coL As Range
    Set coL = Range("myClm")
    Worksheets(coL.Worksheet.Name).Range(coL)).Delete
    I think the integer definition would delete the row versus the column.

    Or perhaps:

    Code:
    Dim n As Integer, coL As Range
    Set coL = Range("myClm")
    n = coL.column
    Worksheets(coL.Worksheet.Name).Columns(n).EntireColumn.Delete
    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-04-04 19:21 ]

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 17:29, Jay Petrulis wrote:
    In your worksheet_change event place the following:

    ---------------
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column > 26 And Target.Column < 32 Then
    If Target.Row = 13 Then
    Call Module2.clear1(Target.Address)
    End If
    If Target.Row = 14 Then
    Call Module2.clear2(Target.Address)
    End If
    If Target.Row = 15 Then
    Call Module2.clear3(Target.Address)
    End If
    End If
    End Sub
    --------------------

    Change the target.column range to suit your needs.

    Post back if this doesn't work for you.

    Bye,
    Jay

    Yee Gads! Have I ever screwed this up! You're absolutely right...I want to adjust for ROW deletion and insertion, not COLUMN.

    That's probably why I have the question below...

    I'm absolutely clueless when it comes to code, so please forgive my ignorance here.

    I get the "module" part of this, as it pertains to relative values. But the "worksheet_change" has me a little confused. We're still referring to absolutes when we call out numbers like "13, 14 & 15" do we not? So if I delete row 8, do 13, 14 & 15 change to 12, 13 & 14?.

    Also, the column range throws me a little. Why are these values listed as numbers instead of letters? And should I set the column range so it covers the entire width of my spreadsheet? (Obviously if I'm deleting or inserting a row, it does so over the entire width of the sheet.)

    Thanks for all your help, guys.

    [ This Message was edited by: bobmc on 2002-04-04 22:39 ]

  4. #14
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I must admit, I was quite thrown off, but I think I'm following you now. You'll want to highlight the first of your three rows in question, insert the name myrow. Use absolute references. Then you'll want to right-click the sheet in question and paste the following code (this will clear the entire row):

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Integer, p As Integer, o As Integer, rw As Range
    Set rw = Range("myrow")
    n = rw.Row
    p = n + 1
    o = n + 2
    If Target.Address = Range("ae" & n).Address Then _
    Range(p & ":" & o).ClearContents
    If Target.Address = Range("ae" & p).Address Then _
    Range(Range(n & ":" & n), Range(o & ":" & o)).ClearContents
    If Target.Address = Range("ae" & o).Address Then _
    Range(n & ":" & p).ClearContents
    End Sub
    Where n is the first row, p is the next row down and o is third row down. Incidentally, also my initials .

    The code below will just clear the contents of the 'other two' cells in question in column AE:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Integer, p As Integer, o As Integer, rw As Range
    Set rw = Range("myrow")
    n = rw.Row
    p = n + 1
    o = n + 2
    If Target.Address = Range("ae" & n).Address Then _
    Range("ae" & p & ":" & "ae" & o).ClearContents
    If Target.Address = Range("ae" & p).Address Then _
    Range("ae" & n & "," & "ae" & o).ClearContents
    If Target.Address = Range("ae" & o).Address Then _
    Range("ae" & n & ":" & "ae" & p).ClearContents
    End Sub
    Either way, inserting or deleting rows above myrow will offset the three rows. Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: nateo on 2002-04-05 11:45 ]

  5. #15
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think I'll need a vacation after this post. But I think the best way to do this is to actually define a cell, not a row. That way if you insert or delete either columns or rows, your 3-cell area will update. Pick the top cell of the 3 and name it mycell. Then right-click on the worksheet in question and place the following code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = Range("mycell").Address Then
    Range(Range("mycell").Offset(1).Address).ClearContents
    Range(Range("mycell").Offset(2).Address).ClearContents
    End If
    If Target.Address = Range(Range("mycell").Offset(1).Address).Address Then
    Range(Range("mycell").Address).ClearContents
    Range(Range("mycell").Offset(2).Address).ClearContents
    End If
    If Target.Address = Range(Range("mycell").Offset(2).Address).Address Then
    Range(Range("mycell").Address).ClearContents
    Range(Range("mycell").Offset(1).Address).ClearContents
    End If
    Application.EnableEvents = True
    End Sub

  6. #16
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 13:02, NateO wrote:
    I think I'll need a vacation after this post. But I think the best way to do this is to actually define a cell, not a row. That way if you insert or delete either columns or rows, your 3-cell area will update. Pick the top cell of the 3 and name it mycell. Then right-click on the worksheet in question and place the following code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = Range("mycell").Address Then
    Range(Range("mycell").Offset(1).Address).ClearContents
    Range(Range("mycell").Offset(2).Address).ClearContents
    End If
    If Target.Address = Range(Range("mycell").Offset(1).Address).Address Then
    Range(Range("mycell").Address).ClearContents
    Range(Range("mycell").Offset(2).Address).ClearContents
    End If
    If Target.Address = Range(Range("mycell").Offset(2).Address).Address Then
    Range(Range("mycell").Address).ClearContents
    Range(Range("mycell").Offset(1).Address).ClearContents
    End If
    Application.EnableEvents = True
    End Sub
    Hi,

    Yet *another* way. I believe that this will handle all scenarios, unless a single cell gets shifted and causes the range to be not a single column/row.

    1. Define the range. I called it "MyRange" here. The referencing was absolute.

    2. Enter the following in the sheet module
    --------------------
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim isect, temp

    Application.EnableEvents = False

    Set isect = Application.Intersect(Range("MyRange"), Target)
    If Not isect Is Nothing Then
    temp = Target.Value
    Range("MyRange").ClearContents
    Target = temp
    End If
    Application.EnableEvents = True

    End Sub
    ----------------------

    This takes the entered value and stores it to the temp variable. The entire range is then cleared and the temp variable is placed in the target cell.

    This will work if rows and columns are inserted above/below/right/left or inside the range (e.g. MyRange is AE10:AE20 and a new row 12 is added, MyRange becomes AE10:AE21).

    Bye,
    Jay

  7. #17
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Likin' this Jay. Kudos.

    Have a great weekend!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com