?
Solved

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

Posted on 2003-02-24
4
Medium Priority
?
7,347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

777 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