[dba-Tech] A couple of Excel questions

Hi Arthur:

A couple of years ago I wrote a full excel spreadsheet application that
allowed users to add and delete records for a company in Toronto. The whole
code was written in VBA...

Add or Update row:

Private Function SaveResults(bolAddingRecord As Boolean) As Boolean
	
 If ValidateData() = True Then
  ' Will a new row need to be added?
  If bolAddingRecord = True Then
    'Go to top row and loop until an empty space is found    
    If Not IsEmpty(ActiveCell) = True Then
     Do  'Find first blank space
      ActiveCell.Offset(1, 0).Select
      Loop Until IsEmpty(ActiveCell) = True
     End If
            
     bolAddingRecord = False
        
  End If

  ' Write content from selected combo box and do all cell formatting here.
  On Error resume next 
  ActiveCell.Value = cmbAccountCode.Value

...

'.........................................................................

Delete Row:

' Method 1: This is the simplest way to delete records but it deletes the
entire row contents which may not be what you want.

Public Function DeleteRecord()

 If MsgBox("Do you wish to DELETE the current Record?", vbExclamation +
vbOKCancel, "Delete current Record") = vbOK Then

  If IsEmpty(ActiveCell) = False Then
   Selection.EntireRow.Delete
            
   If IsEmpty(ActiveCell) = True Then
    
    ' Variable that hold record count
    ' lngRecords = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count - 1	
    If lngRecords > 1 Then 
     lngPosition = lngPosition - 1
     ActiveCell.Offset(-1, 0).Select
    End If

   End If
  End If
 End If

End Function

'.........................................................................
' Method 2: Deletes a range of columns by moving the contents from the rows
below up one row.

Public Function DeleteRecord()

 If MsgBox("Do you wish to DELETE the current Record?", vbExclamation +
vbOKCancel, "Delete current Record") = vbOK Then

  If IsEmpty(ActiveCell) = False Then
    MoveRange
    lngRecords = lngRecords - 1
            
    If IsEmpty(ActiveCell) = True Then
     If lngRecords > 1 Then
       lngPosition = lngPosition - 1
       ActiveCell.Offset(-1, 0).Select
     End If
   End If
  End If
        
  frmEntry.lblRecordPosition.Caption = "Record " & lngPosition & " of " &
lngRecords
   
 End If

End Function

Public Function MoveRange()
  Dim lngRowPosition As Long
  Dim lngRowCount As Long
  Dim strCell As String
        
  lngRowPosition = Application.ActiveCell.Row
  lngRowCount = 0
  strCell = "a" & lngRowPosition
    
  Application.ScreenUpdating = False
    
  lngRowCount = GetRecordPosition()
 
  Range(strCell).Select
    
  Selection.CurrentRegion.Select
  Selection.Offset(lngRowPosition, 0).Resize(lngRowCount - lngRowPosition +
2).Select
  Selection.Copy
    
  Range(strCell).Select
    
  ActiveSheet.Paste
  Application.CutCopyMode = False
    
  Application.ScreenUpdating = True

  Range(strCell).Select
    
End Function

Public Function GetRecordPosition() As Integer
  Dim intCounter As Integer
    
  Range("a2").Select
    
  intCounter = 0
    
  Do
    If IsEmpty(ActiveCell) = False Then
      intCounter = intCounter + 1
      ActiveCell.Offset(1, 0).Select
    End If
  Loop Until IsEmpty(ActiveCell) = True
    
  Range("a2").Select
    
  GetRecordPosition = intCounter
    
End Function


This code was specifically written for a hard coded application so the
offsets are not using variables but I hope this helps. 

Jim  


-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, December 24, 2007 7:00 AM
To: Discussion of Hardware and Software issues
Subject: [dba-Tech] A couple of Excel questions

I have a workbook containing 3 tables side by side. The range of table 1 is
A3:D15, of table 2 it's F3:F15, of table 3 it's K3:N15.

What I need to do is lose Row 3 and then insert a new row at Row 15. But
because the table has borders, what was row 15 orignally now has a bottom
edge, and the new row 15 doesn't. I know how to do a selection and kill its
formatting, and then do another selection and give it a bottom border, but
that is so clumsy.

First of all, what are commands to delete a row and to insert a row?

Second, it happens that frequently some rows in the table have not been
filled yet. In that case, I'd like to find the first blank row  and do the
row insert there, thus simplifying the the formatting code

Third, how to you copy just the value from a cell to another cell, without
copying the formatting too?

Thanks and Happy Holidays to all.
Arthur
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com