Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Thanks, Jim, but still no luck. What is most strange is I have the code
below in another sub that works fine (this one loads the selection form which includes the code that is not working tied to a specific selection made). I've tested to see if I'm passing the test for the first If/Then statement and that is working but something between the For/Next statements are failing. ================== Private Sub MeasDateMill1_Change() 'Check to see if data with same date/mill have already been entered in the database Dim rngA As Range Dim dataws As Worksheet Set dataws = Worksheets("Data") With Worksheets("Data") Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With 'CODE TO FIND DUPLICATE DATA For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row If dataws.Cells(i, 1).Value = MeasDateMill1 And _ dataws.Cells(i, 2).Value = "Mill 1" Then Dim ans As Long ans = MsgBox("Measurements for this date on this mill already exist in the Database.", vbOKOnly) ' ExistingDataFound.Show Eliminating this pop up until can figure out how to solve problem of overwriting data ' For the time being will give a pop up only telling the user there is data with this date in the db already ' Mill1DuplicateFound.Show Exit Sub ' End If Next i End Sub "Jim Rech" wrote: Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) This sets a one cell range equal to the last cell in A with an entry. You have to specifiy with the begins and ends: Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp)) -- Jim "Brian" wrote in message ... |I am trying to delete rows if column A has value X and column B has value Y. | My If test does not seem to be working in this particular sub (although I | have virtually identical code in another sub that does work). I'm at a loss. | Code is below... | | If ExistingDataFound.Overwrite.Value = True Then | Dim rngA As Range | Dim dataws As Worksheet | Set dataws = Worksheets("Data") | With Worksheets("Data") | Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) | End With | 'CODE TO FIND DUPLICATE DATA | For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row | If dataws.Cells(i, 1).Value = MeasDateMill1 And _ | dataws.Cells(i, 2).Value = "Mill 1" Then | Rows(i).Delete Shift:=xlUp | Unload ExistingDataFound | Exit Sub | End If | Next i | End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
I figured out my problem. The test for "MeasDateMill1" is from another form
and for a reason I don't know is causing the problem. I can work around this, though. Next problem is I'm getting an application or object not defined errow on the "rows(i).Delete line. I've also tried without success: Rows ("i:i").Select Selection.Delete Shift:xlUp "Brian" wrote: Thanks, Jim, but still no luck. What is most strange is I have the code below in another sub that works fine (this one loads the selection form which includes the code that is not working tied to a specific selection made). I've tested to see if I'm passing the test for the first If/Then statement and that is working but something between the For/Next statements are failing. ================== Private Sub MeasDateMill1_Change() 'Check to see if data with same date/mill have already been entered in the database Dim rngA As Range Dim dataws As Worksheet Set dataws = Worksheets("Data") With Worksheets("Data") Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With 'CODE TO FIND DUPLICATE DATA For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row If dataws.Cells(i, 1).Value = MeasDateMill1 And _ dataws.Cells(i, 2).Value = "Mill 1" Then Dim ans As Long ans = MsgBox("Measurements for this date on this mill already exist in the Database.", vbOKOnly) ' ExistingDataFound.Show Eliminating this pop up until can figure out how to solve problem of overwriting data ' For the time being will give a pop up only telling the user there is data with this date in the db already ' Mill1DuplicateFound.Show Exit Sub ' End If Next i End Sub "Jim Rech" wrote: Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) This sets a one cell range equal to the last cell in A with an entry. You have to specifiy with the begins and ends: Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp)) -- Jim "Brian" wrote in message ... |I am trying to delete rows if column A has value X and column B has value Y. | My If test does not seem to be working in this particular sub (although I | have virtually identical code in another sub that does work). I'm at a loss. | Code is below... | | If ExistingDataFound.Overwrite.Value = True Then | Dim rngA As Range | Dim dataws As Worksheet | Set dataws = Worksheets("Data") | With Worksheets("Data") | Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) | End With | 'CODE TO FIND DUPLICATE DATA | For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row | If dataws.Cells(i, 1).Value = MeasDateMill1 And _ | dataws.Cells(i, 2).Value = "Mill 1" Then | Rows(i).Delete Shift:=xlUp | Unload ExistingDataFound | Exit Sub | End If | Next i | End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
"i:i" isn't a row it's a column.
-- Jim "Brian" wrote in message ... |I figured out my problem. The test for "MeasDateMill1" is from another form | and for a reason I don't know is causing the problem. I can work around | this, though. Next problem is I'm getting an application or object not | defined errow on the "rows(i).Delete line. I've also tried without success: | | Rows ("i:i").Select | Selection.Delete Shift:xlUp | | "Brian" wrote: | | Thanks, Jim, but still no luck. What is most strange is I have the code | below in another sub that works fine (this one loads the selection form which | includes the code that is not working tied to a specific selection made). | I've tested to see if I'm passing the test for the first If/Then statement | and that is working but something between the For/Next statements are failing. | | ================== | Private Sub MeasDateMill1_Change() | 'Check to see if data with same date/mill have already been entered in the | database | Dim rngA As Range | Dim dataws As Worksheet | Set dataws = Worksheets("Data") | With Worksheets("Data") | Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) | End With | 'CODE TO FIND DUPLICATE DATA | For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row | If dataws.Cells(i, 1).Value = MeasDateMill1 And _ | dataws.Cells(i, 2).Value = "Mill 1" Then | Dim ans As Long | ans = MsgBox("Measurements for this date on this mill already exist | in the Database.", vbOKOnly) | ' ExistingDataFound.Show Eliminating this pop up until can figure | out how to solve problem of overwriting data | ' For the time being will give a pop up only telling the user there | is data with this date in the db already | ' | Mill1DuplicateFound.Show | Exit Sub | ' | End If | Next i | End Sub | "Jim Rech" wrote: | | Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) | | This sets a one cell range equal to the last cell in A with an entry. | | You have to specifiy with the begins and ends: | | Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp)) | | -- | Jim | "Brian" wrote in message | ... | |I am trying to delete rows if column A has value X and column B has value | Y. | | My If test does not seem to be working in this particular sub (although I | | have virtually identical code in another sub that does work). I'm at a | loss. | | Code is below... | | | | If ExistingDataFound.Overwrite.Value = True Then | | Dim rngA As Range | | Dim dataws As Worksheet | | Set dataws = Worksheets("Data") | | With Worksheets("Data") | | Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp) | | End With | | 'CODE TO FIND DUPLICATE DATA | | For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st | row | | If dataws.Cells(i, 1).Value = MeasDateMill1 And _ | | dataws.Cells(i, 2).Value = "Mill 1" Then | | Rows(i).Delete Shift:=xlUp | | Unload ExistingDataFound | | Exit Sub | | End If | | Next i | | End If | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |