Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
From the help screens (Worksheet.rows property), I am trying to adapt this
code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
i don't use 07 so there may be a difference here, but if i were doing
what you are doing i would dim rw as range. if that fixes that problem, then you're going to bomb because you haven't declared "this" and "last". hope that helps a little. if not, well, i tried. :) susan On Apr 15, 1:16*pm, owlnevada wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. *I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. *Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? *It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows * * this = rw.Cells(1, 1).Value * * If this = "" Then rw.Delete * * last = this Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
Still gives an "Object Required" error when I dim as follows"
Sub DeleteRows() Dim rw As Range Dim this As String, last As String For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub "Susan" wrote: i don't use 07 so there may be a difference here, but if i were doing what you are doing i would dim rw as range. if that fixes that problem, then you're going to bomb because you haven't declared "this" and "last". hope that helps a little. if not, well, i tried. :) susan On Apr 15, 1:16 pm, owlnevada wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
DIM rw as RANGE
DIM this as STRING DIM last as STRING ---- NOTE in old VB one used to write DIM this, last as string and both variables would be type string. However, if you wrote this in VBA, then the variable 'this' would default to type variant so DIM this, last as strting is in fact DIM this as variant, last as strting so to be sure and explicitly dimension every variable's type. "owlnevada" wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
There is no activeworksheet object in excel. It's ActiveSheet.
Another problem is that when you delete rows and start at the top, it becomes a mess. You'll see this in your testing. The easiest alternatives are to start at the bottom and work up. Or start at the top and work down, but build a range that will be deleted at the end: Option explicit sub test1() dim iRow as long dim LastRow as long with activesheet.range("a1").currentregion lastrow = .rows(.rows.count).row end with for irow = lastrow to firstrow step -1 if .cells(irow,"A").value = "" then .rows(irow).delete end if next irow end sub sub test2() dim myCell as range dim myRng as range dim delRng as range set myrng = activesheet.range("a1").currentregion.columns(1) set delrng = nothing for each mycell in myrng.cells if mycell.value = "" then if delrng is nothing then set delrng = mycell else set delrng = union(mycell, delrng) end if end if next mycell if delrng is nothing then msgbox "nothing to delete" else delrng.entirerow.delete end if end sub Both uncompiled and untested. Watch for typos. ======= You may find sorting your range by column A and putting the empty cells together, then deleting that single contiguous range even quicker than using a macro. owlnevada wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
If you want to delete only the cells in column A
that are blank, then: Sub delBlanks() lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set myRange = Range("A1:A" & lr) myRange.SpecialCells(xlCellTypeBlanks).Delete End Sub If you want to delete entire rows. Sub delRows() lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set myRange = Range("A1:A" & lr) For i = lr To 1 Step -1 If Range("A" & i) = "" Then Rows(i).Delete End If Next End Sub "owlnevada" wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
If column A will always have data for those rows you want to keep and always
not have data for those rows you want to delete, use ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlan ks).EntireRow.Delete -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 2007 double-click to format may not work; right click and select from the menu "owlnevada" wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
Hi,
Looking at your example code, the object error that you receive is most likely because you haven't created and Set the row object itself. Sub DeleteRows() Dim rw As Object Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows The line using the Set keyword will establish the nature of the object in the loop. When starting the Loop, the .rows after CurrentRegion should not be present as the nature of rw implies this. This is my first post so please don't pounce, but I do hope that this helps. Dave "owlnevada" wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the correct object?
This code runs fine with no errors but doesn't do a thing. Not certain why????
"thinfrog4" wrote: Hi, Looking at your example code, the object error that you receive is most likely because you haven't created and Set the row object itself. Sub DeleteRows() Dim rw As Object Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub Set rw = ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows The line using the Set keyword will establish the nature of the object in the loop. When starting the Loop, the .rows after CurrentRegion should not be present as the nature of rw implies this. This is my first post so please don't pounce, but I do hope that this helps. Dave "owlnevada" wrote: From the help screens (Worksheet.rows property), I am trying to adapt this code to do something similar. I have a worksheet of mailing lists with all the data stripped from a single column(A) to fill columns B,C,D etc with each item so that now only every 4th row has the data (in cols A:D) and I need to delete the blank rows (2:4, 6:8, 10:12, etc). Am abit puzzled as to what the "object required" error needs after defining the rw as object. Am using Excel 2007. I need to dimension all the variables with Option Explicit so what am I missing here? It needs to check each row for no data in case of some 5 line addresses rather than 4 so all data is preserved and not accidentally deleted. Thanks in advance for your help. Sub DeleteRows() Dim rw As Object For Each rw In ActiveWorksheet.Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = "" Then rw.Delete last = this Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get a chart object (excel.ChartObject) from OLE_chart.object | Excel Programming | |||
Selecting correct MS Outlook Object Library (repeat) | Excel Programming | |||
Selecting correct MS Outlook Object Library | Excel Programming | |||
Option button object proeprties or object not found in vba | Excel Programming | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming |