1 Replies - 1741 Views - Last Post: 15 October 2012 - 01:20 PM Rate Topic: -----

#1 fdegree  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-October 12

Problem Converting VBA to VB.Net

Posted 12 October 2012 - 04:55 PM

I am using VS 2008, and coding in VB.Net. I have created an Excel 2007 Ribbon Add-In with various custom buttons that perform a multitude of functions. The most recent task I am trying to overcome is proving to be rather frustrating.

I want a button click event to perform a loop through each cell, in each row, of the active worksheet. Checking to see if the cell has four borders AND checking to see if the cell is empty. If both conditions are met, then I want the entire row to be deleted, regardless of the content in any other cell in that row. In other words, if any 1 cell has four borders AND is empty, the entire row is deleted, regardless of the conditions in any other cell in that row.

Both conditions must be met...if a cell meets just one condition, then I want the row to remain.

I have a macro that is performing this task, but I have been unable to convert it to VB.Net. Here is the Macro:
Sub DeleteEmptyRows()
Dim i As Long, j As Long, firstRow
Dim rng As Range, rRow As Range, cel As Range
Set rng = ActiveSheet.Range("A2:AK55")
rng.Interior.ColorIndex = xlNone
     firstRow = rng.Rows(1).Row
     ReDim arrDelRows(1 To rng.Rows.Count) As Boolean
     For i = 1 To rng.Rows.Count
             For Each cel In rng.Rows(i).Cells
                     If Len(cel) = 0 Then
                         If cel.MergeArea(1).Address = cel.Address Then
                             arrDelRows(i) = True
                             For j = 7 To 10
                                     If cel.Borders(j).LineStyle = xlLineStyleNone Then
                                             arrDelRows(i) = False
                                             Exit For
                                     End If
                             Next
                             If arrDelRows(i) Then
                                     cel.Interior.Color = vbYellow
                                     rng(i, 1) = cel.Address
                                     Exit For
                             End If
                         End If
                     End If
             Next
     Next
     For i = UBound(arrDelRows) To 1 Step -1
             If arrDelRows(i) Then
                 ActiveSheet.Rows(firstRow - 1 + i).Delete
             End If
     Next
End Sub

I have tried to copy/paste this Macro into VS 2008 and then fix any errors that arose from intellisense. I was able to eliminate the errors, but when the associated button is clicked, nothing happens. Here is the VB.Net code that I "fixed":
        Dim style As Excel.Style = Globals.ThisAddIn.Application.ActiveWorkbook.Styles.Add("NewStyle")
        Dim i As Long, j As Long
        Dim firstRow As Integer
        Dim rng As Range, rRow As Range, cel As Range
        rng = Globals.ThisAddIn.Application.ActiveSheet.Range("A2:AK55")
        rng.Interior.ColorIndex = 0
        firstRow = rng.Rows(1).Row
        Dim arrDelRows(0 To rng.Rows.Count) As Boolean
        For i = 1 To rng.Rows.Count
            For Each cel In rng.Rows(i).Cells
                If Len(cel) = 0 Then
                    If cel.MergeArea(1).Address = cel.Address Then
                        arrDelRows(i) = True
                        For j = 7 To 10
                            If cel.Borders(j).LineStyle = Excel.XlLineStyle.xlLineStyleNone Then
                                arrDelRows(i) = False
                                Exit For
                            End If
                        Next
                        If arrDelRows(i) Then
                            style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
                            rng(i, 1) = cel.Address
                            Exit For
                        End If
                    End If
                End If
            Next
        Next
            For i = UBound(arrDelRows) To 1 Step -1
                If arrDelRows(i) Then
                      Globals.ThisAddIn.Application.ActiveSheet.Rows(firstRow - 1 + i).Delete()
                End If
            Next

Unfortunately, I am unable to debug with breakpoints. Whenever I hit F5, a warning box pops up telling me I don't have the proper Office version installed. I am writing this VB.net code to create a Ribbon Add-In for Excel 2007, but I have Excel 2010 on my computer. Since I don't have Excel 2007, VS 2008 won't allow me to debug.

I am rather new to programming and VB.net. Although, I have learned a lot recently while building this Ribbon Add-In. But, this problem seems to be kicking my butt.

Any suggestions?

Thanks!!!

Is This A Good Question/Topic? 0
  • +

Replies To: Problem Converting VBA to VB.Net

#2 fdegree  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-October 12

Re: Problem Converting VBA to VB.Net

Posted 15 October 2012 - 01:20 PM

Anyone?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1