Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA - Range(xx).Rows.Insert sometimes overwrites, sometimes not

Posted on 2003-02-24
4
Medium Priority
?
7,354 Views
Last Modified: 2008-02-07
I have an automated Excel spreadsheet that pulls in data from an Oracle database.  My table is sorted by Name, alphabetically.  The users want one name to be taken out of alphabetical order and always appear on the top.  To do this, there is code written to find this line and move it to the top.  The code is below:

Cells(1, 1).Select
Do While ActiveCell.Row < intLastRow And bSegmentAverage = False
   Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
   If ActiveCell.Value = "SEGMENT AVERAGE" Then
      intSegmentRow = ActiveCell.Row
      Range(ActiveCell.Address, Cells(ActiveCell.Row + 2, intLastColumn)).Select
      Selection.Cut
      bSegmentAverage = True
      intSegmentRow = ActiveCell.Row
      Range(Cells(8, 1), Cells(10,intLastColumn)).Rows.Insert
   End If
Loop

Now, usually the first time this is run, it works perfectly.  It takes the range of cells I want to move and inserts them at the spot I want, moving all the other rows down.  What seems to happen, though, is the second time the program is run it has a tendency to try to overwrite the cells where I am trying to insert, rather than insert them and move the lower rows down.  

Is there a better way to do this or can I specify somehow to never overwrite the existing contents and always shift the rows to below?  

I am new to using VBA with Excel and this is my first post on EE, so please excuse me if I am unclear.

Thanks, Jody
0
Comment
Question by:jodyjf
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
sebastienm earned 200 total points
ID: 8010757
Jody,
I havn't time to look at your code, but the following should work.

'---------------------------------------------
Sub test()
   Dim Rg As Range
   Dim RgFound As Range
   Dim s As String
   
   s = "SEGMENT AVERAGE"
   Set Rg = Range("A1:A30")         '<------  change here
   
   Set RgFound = Rg.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
   If Not RgFound Is Nothing Then      ' if the string is found
      If RgFound.Address <> Rg.Cells(1).Address Then 'if found cell is not first (else nothing to do)
         Range(Rg.Cells(1), RgFound.Offset(-1, 0)).Copy Rg.Cells(2, 1)
         Rg.Cells(1) = s
         Application.CutCopyMode = False
      End If
   End If
End Sub
'---------------------------------------

Regards,
Sebastien
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8010834
ouch... seems like the table has several column... while my code only works if 1 column only. So instead (case of several column): it finds the "SEGment..." cell and insert it in place of the first row.

Sub test()
   Dim Rg As Range
   Dim RgFound As Range
   Dim s As String
   
   s = "SEGMENT AVERAGE"
   Set Rg = Range("A1:A30")         '<------  change here; column to search
   
   Set RgFound = Rg.Find(what:=s, LookIn:=xlValues, lookat:=xlWhole)
   If Not RgFound Is Nothing Then
      If RgFound.Address <> Rg.Cells(1).Address Then 'found cell is not first (else nothing to do)
         RgFound.EntireRow.Cut
         Rg.Cells(1).EntireRow.Insert Shift:=xlDown
         Application.CutCopyMode = False
      End If
   End If
End Sub
'-------------------------------------------

Sorry about the confusion.
I hope this will work for you.
Sebastien
0
 

Author Comment

by:jodyjf
ID: 8010862
Thanks for taking the time.  Your method will probably work better and I am saving this for future reference.  In the meantime, the simple solution I needed was just to specify the Shift:=xlDown after the .Insert method.  

Thanks,
Jody
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8010910
Thank you for the points, Jody.

Glad i could help,
Sebastien
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question