Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using VBA to Eliminate Rows (2003)

    Using VBA, how can you eliminate the rows, in the attached file, for which the C column cells contain text rather than numbers(keeping the first row as header though)?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Try this:

    Sub DeleteTextRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 2 Step -1
    If Not IsNumeric(Range("C" & r)) Then
    Range("A" & r).EntireRow.Delete
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub

    When deleting rows, you must loop backwards to avoid problems. The loop stops at row 2 because you want to keep the headers in row 1.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Perfect as usual. Thank you so much Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    1) Your code is dangerous, since you have unqualified references to Range. This will create a second instance of Excel that will remain in memory after the code has finished. Change all instances of Range(...) to xlApp.Range(...) or to xlWbk.Worksheets(1).Range(...)

    2) Instead of testing for Not IsNumeric(...), test the actual contents of the cell, for example in a Select Case ... End Select block. You'd have to keep track of "Power down", :New day" and "Power up".

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    I've modified your code so that I can run it from within an Access file:
    <pre>Sub DeleteTextRows()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim blnStart As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If
    On Error GoTo ErrHandler
    If xlApp.Dialogs(xlDialogOpen).Show = False Then
    GoTo ExitHandler
    End If
    Set xlWbk = xlApp.ActiveWorkbook
    Dim r As Long
    Dim m As Long
    xlApp.ScreenUpdating = False
    m = Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 2 Step -1
    If Not IsNumeric(Range("C" & r)) Then
    Range("A" & r).EntireRow.Delete
    End If
    Next r
    xlApp.ScreenUpdating = True
    ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    If blnStart Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    'Screen.ActiveForm.Repaint
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub </pre>


    Looking at the attached VAPRIO GR1 2-07.xls, how can I modify the code so that, when there's a power down in column C, the rows in the output file get filled with the missing days and hours so that the end result still contains all the rows for the month in question as in the attached Calvi.xls?
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Thank you Hans,
    I've followed your suggestion:
    <pre>Sub DeleteTextRows()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim blnStart As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If
    On Error GoTo ErrHandler
    If xlApp.Dialogs(xlDialogOpen).Show = False Then
    GoTo ExitHandler
    End If
    Set xlWbk = xlApp.ActiveWorkbook
    Dim r As Long
    Dim m As Long
    xlApp.ScreenUpdating = False
    m = xlWbk.Worksheets(1).Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 2 Step -1
    Select Case xlWbk.Worksheets(1).Range("C" & r)
    Case "New day"
    xlWbk.Worksheets(1).Range("A" & r).EntireRow.Delete
    Case "Power down"

    Case "Power up"

    End Select
    Next r
    xlApp.ScreenUpdating = True
    ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    If blnStart Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    'Screen.ActiveForm.Repaint
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</pre>


    What is the Excel method that allows you to insert rows? I've tried searching for InsertRow(s) to no avail and also how do you add 15' to the values in the A column? I was planning on using a loop to insert as many rows as there are quarter hours between the power down and power up times.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    There is no InsertRows method. Just like you use SomeRange.EntireRow.Delete to delete a row or rows, you use SomeRange.EntireRow.Insert to insert a row or rows.

    Using text values for the times makes it more difficult than necessary to manipulate them. I would parse the text value, convert it to a time, use DateAdd to add 15 minutes, then convert back to a text value again.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Thank you Hans,
    Just to get a headstart, I've formatted column A values as time and proceeded to see if I could at least insert a row where a power up occurs and fill the relevant A column cell with a time 15' less than the cell below it but when I run the sub below, I get the message:
    Application-defined or object-defined error
    <pre>Sub DeleteTextRows()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim blnStart As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If
    On Error GoTo ErrHandler
    If xlApp.Dialogs(xlDialogOpen).Show = False Then
    GoTo ExitHandler
    End If
    Set xlWbk = xlApp.ActiveWorkbook
    Dim r As Long
    Dim m As Long
    xlApp.ScreenUpdating = False
    m = xlWbk.Worksheets(1).Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 2 Step -1
    Select Case xlWbk.Worksheets(1).Range("C" & r)
    Case "New day"
    xlWbk.Worksheets(1).Range("A" & r).EntireRow.Delete
    Case "Power down"
    xlWbk.Worksheets(1).Range("A" & r).EntireRow.Delete
    Case "Power up"
    xlWbk.Worksheets(1).Range("A" & r).EntireRow.Delete
    xlWbk.Worksheets(1).Range("A" & r).EntireRow.Insert
    xlWbk.Worksheets(1).Cells(m, 1) = _
    DateAdd("n", -15, xlWbk.Worksheets(1).Cells(m + 1, 1))
    End Select
    Next r
    xlApp.ScreenUpdating = True
    ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=True
    Set xlWbk = Nothing
    If blnStart Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    'Screen.ActiveForm.Repaint
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</pre>

    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Correction, I used the wrong variable in the loop, now it works.
    ....
    xlWbk.Worksheets(1).Cells(r, 1) = _
    DateAdd("n", -15, xlWbk.Worksheets(1).Cells(r + 1, 1))
    ......

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    In

    xlWbk.Worksheets(1).Cells(m, 1) = _
    DateAdd("n", -15, xlWbk.Worksheets(1).Cells(m + 1, 1))

    m is the row number of the last filled row when the macro started. You will have deleted rows since then, so row m will now be empty, and xlWbk.Worksheets(1).Cells(m + 1, 1)) does not contain a valid time.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Hans,
    I've found out the Sub DeleteTextRows() works even if the A column isn't formatted as date/time.
    Not only do I need a loop to insert as many rows as there are quarter hours between the power down and power up times. I need an additional outer loop to take into account there could also be a day difference between the power down and power up times.
    So, right after the
    Case "Power up"
    code line, I intend to store the date value in the B column beside the cell containing the "Power down" string which is immediately above the cell containing the "Power up" string which causes the
    Case "Power up"
    line to fire.
    How do I look that value up?
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    When you are at "Power down" or "Power up", the variable r contains the row number. So the date is

    xlWbk.Worksheets(1).Range("B" & r)

    You can store this in a variable.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    But the problem is I need to know the value in the B column beside the cell containing the "Power down" string when I'm at "Power up" so that I know how many rows I have to insert before I get to "Power down" so using
    xlWbk.Worksheets(1).Range("B" & r)
    when I'm at "Power up" won't do it because it gives me the date beside "Power up".

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to Eliminate Rows (2003)

    When you're at "Power Up", store the "Power Up" date in a variable. Don't insert anything yet.
    Continue looping until you encounter the corresponding "Power Down".
    Now you know the "Power Down" date, so you can calculate how many rows you must insert.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to Eliminate Rows (2003)

    Thank you Hans,
    One final stumbling block(or so it seems <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) in the code portion for inserting values when there's a day difference between the Power down and Power up times; how can I set the following line
    Do While c < #12:00:00 AM#
    so that the loop statements are executed?
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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