Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 28th, 2008, 09:59 AM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default entireRow delete problem

I've got an Excel spreadsheet with 55,000 rows. The name in the third colum repeats for different colleagues in the 13th column. I've written the following VB code to append the colleague name and delete the repeat rows. It works fine until it gets to the 4,000th row. After that the deletes don't work and the program loops appending the same name to the 13th column. There's nothing different about the 4,000th row but I've deleted it as a test. But to no avail. The statement Selection.Rows(rwIndex).EntireRow.Delete does not work when rwIndex gets to 4,000. Does anyone have a suggestion?

Sub CompanyCoverage()
'
'
Dim rwIndex As Integer
Dim prev_name As String
Sheet1.Select
Worksheets("CompanyCoverage1").Select

rwIndex = 1
prev_name = Cells(rwIndex, 3).Value
MsgBox " prev_name is " & prev_name
rwIndex = rwIndex + 1

Do Until rwIndex > 4100
If Cells(rwIndex, 3).Value <> prev_name Then
prev_name = Cells(rwIndex, 3).Value
rwIndex = rwIndex + 1
Else
Cells(rwIndex - 1, 13).Value = Cells(rwIndex - 1, 13).Value & "; " & Cells(rwIndex, 13).Value
'MsgBox " index is " & rwIndex
Selection.Rows(rwIndex).EntireRow.Delete
End If

Loop
End Sub


Local Time: 09:58 AM
Local Date: 02-28-2008
Location:




There are 10 kinds of people in the world - those that understand binary and those that don't.
Reply With Quote
  #2 (permalink)  
Old March 4th, 2008, 07:14 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

cronid,

To be able to access all the rows in:
Excel 2003 contains 65,536 rows.
Excel 2007 contains 1,048,567 rows.

Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767.

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

I would change this line of code:
Dim rwIndex As Integer

to this:
Dim rwIndex As Long


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.
Reply With Quote
  #3 (permalink)  
Old March 4th, 2008, 07:20 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

cronid,

One more thing.

This line of code:
Do Until rwIndex > 4100

may stop around row 4000 if the macro has already deleted 100 or more rows.

When deleting rows, I usually start from the last row of data to the first, with a For Next loop:
For lngLoopCtr = lngLastRow To 1 Step -1
    'your code goes here
Next lngLoopCtr


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete query problem aneesh_sunu@yahoo.co.in PHP How-To 2 December 18th, 2006 08:24 AM
problem with delete statement thas123 SQL Server 2000 6 March 23rd, 2006 12:13 PM
Recordset delete question/problem bleutiger Classic ASP Databases 1 February 25th, 2005 06:50 AM
delete problem hosefo81 PHP Databases 1 March 7th, 2004 06:40 AM
Delete from database problem benskywalker Classic ASP Databases 1 September 26th, 2003 02:50 PM



All times are GMT -4. The time now is 02:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.