Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
I have an excel spreadsheet which I want to add two things to a macro I
have. This first thing is I want to search a single column and delete any rows that have 0 (zero) in it. The second thing I want to do is to fill a column with a word (the column heading title) down to the last row. If the fill is done in more or less completed rows then I get an error when I import the data into another application. So I suppose I need code to look for data in say, the first column and stop when it finds a blank. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
The first macro below, select any cell in the column you want to "scan" for zero, and run the macro
'---------------------------------------------------- Sub fine_zero_and_delete_row( Dim tmp As Strin Dim tmp2 As Singl With Selectio tmp = Application.Intersect(.CurrentRegion, ActiveSheet.Columns(.Column)).Addres End Wit With Range(tmp For tmp2 = .Rows.Count To 1 Step - If .Cells(tmp2).Value = 0 Then .Rows(tmp2).Delet Nex End Wit End Su '---------------------------------------------------------------- For the second task, I'm not sure whether you want to "replace" the non-blank cells in a columns, OR, you have a table with many columns and one of them is empty If you want to "replace" non-blank cells, try the one below Select any cell in the column you want to fill (replace non-blank cells), and run the macro '---------------------------------------------------------------- Sub fill_to_bottom( Dim tmp As Singl Application.ScreenUpdating = Fals tmp = With Selection.Cells(1 Do Until .Offset(tmp, 0).Value = " .Offset(tmp, 0).Value = "hello tmp = tmp + Loo End Wit End Su '---------------------------------------------------------------- However, if you have a table and in one of the columns you want to fill the cells with a value. And you want the macro to stop at the LAST ROW of the table, try the following macro Select any cell in the column to fill, and run the macro '---------------------------------------------------------------- Sub fill_to_bottom2( Dim tmp As String, cell As Objec Application.ScreenUpdating = Fals With Selectio tmp = Application.Intersect(Columns(.Column), .CurrentRegion).Addres End Wit With Range(tmp For Each cell In .Cell cell.Value = "hello Nex End Wit End Su '---------------------------------------------------------------- ----- direwolf wrote: ---- I have an excel spreadsheet which I want to add two things to a macro have This first thing is I want to search a single column and delete an rows that have 0 (zero) in it The second thing I want to do is to fill a column with a word (th column heading title) down to the last row. If the fill is done i more or less completed rows then I get an error when I import the dat into another application. So I suppose I need code to look for data in say, the first column an stop when it finds a blank -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Try this shortcut method.
Select the heading cell. At the bottom right of the cell you will see the 'fill handle' - it i a small 'x' in the corner. Double click the fill handle. It will fill down automatically, and stop at the first blank row. This should also remove the zero's as they will be replaced by th heading -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
I know how to do it all manually
The file I want to use this on is formated from its original form via a macro. These last two steps are the ones I can't work out how to include in the macro. I am currently doing them manually. BTW the two different actions I want the macro to do are in different columns. i.e. there is a totals column and a label column --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Try these .
Sub ReplaceZeros() ' replace zeros Columns("D:D").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByColumns, MatchCase:=False End Sub Sub CopyHeading() ' auto fill Dim X As Range Set X = ActiveSheet.[A1] X.AutoFill Destination:=X.Resize(X.End(xlDown).Row, 1) Range("A1:A10").Select End Sub Happy new year. --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Thanks very much for the reply Kieran. :)
The first one replaces to zero in the cell with an empty cell. What I need to do is delete the row that the cell is in. The second one fills the column no problems. But I need it to stop once it reaches the last row. i.e. column A has the data in in column B needs to have the label filled down to the row that the last entry in column A is. I hope I have explained it right. --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
I found this code and tried to get it to work, but I get and *Invalid o
Unqualified reference* error at .Find Code ------------------- Dim c As Range Columns("B").Select Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End Wit ------------------- So I'm guessing that .Find is not a standard functionin VB and needs t be defined or something -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Dim c As Range
With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With -- Regards, Tom Ogilvy "direwolf" wrote in message ... I found this code and tried to get it to work, but I get and *Invalid or Unqualified reference* error at .Find Code: -------------------- Dim c As Range Columns("B").Select Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With -------------------- So I'm guessing that .Find is not a standard functionin VB and needs to be defined or something? --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Thanks that did the trick with deleting the rows with zeros.
Cheers On Wed, 31 Dec 2003 08:27:18 -0500, "Tom Ogilvy" wrote: Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Hmmm
On closer examination this actually deletes the rows that have zero in the figure anywhere i.e. 108 or 500 or 20 will result in the row being deleted. Is there a way to set it so it only deletes the row if the cell contents is zero only and not just containing a zero? Cheers On Wed, 31 Dec 2003 08:27:18 -0500, "Tom Ogilvy" wrote: Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
One way:
Change Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) to Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) In article , (DireWolf) wrote: Hmmm On closer examination this actually deletes the rows that have zero in the figure anywhere i.e. 108 or 500 or 20 will result in the row being deleted. Is there a way to set it so it only deletes the row if the cell contents is zero only and not just containing a zero? Cheers On Wed, 31 Dec 2003 08:27:18 -0500, "Tom Ogilvy" wrote: Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Cool Stuff, Works a treat
Thanks :-) On Thu, 01 Jan 2004 05:27:11 -0700, "J.E. McGimpsey" wrote: One way: Change Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) to Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) In article , (DireWolf) wrote: Hmmm On closer examination this actually deletes the rows that have zero in the figure anywhere i.e. 108 or 500 or 20 will result in the row being deleted. Is there a way to set it so it only deletes the row if the cell contents is zero only and not just containing a zero? Cheers On Wed, 31 Dec 2003 08:27:18 -0500, "Tom Ogilvy" wrote: Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
..find needs to be qualified by a range reference
try Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With --- Message posted from http://www.ExcelForum.com/ |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Thanks Kieran, J.E. McGimpsey gave me this code which is similar to wha
you gave. This one only deletes rows where the result = 0 where as I found you code deletes any row containing 0 i.e. 100 or 308 etc Dim c As Range With Columns("B") Do Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End Wit -- Message posted from http://www.ExcelForum.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Your code is identical to what I posted 4 days ago. What are you trying to
say? Find was qualified by a range reference then and you haven't changed it. -- Regards, Tom Ogilvy Kieran wrote in message ... find needs to be qualified by a range reference try Dim c As Range With Columns("B") Do Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _ MatchCase:=False) If c Is Nothing Then Exit Do c.EntireRow.Delete Loop End With --- Message posted from http://www.ExcelForum.com/ |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Tom,
Sorry if there was any offense. I was replying to the other post http://www.excelforum.com/t177871-s where With Columns("B") was written as Columns("B").Select I can only assume that direworf pasted your code incorrectly. happy new year --- Message posted from http://www.ExcelForum.com/ |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
find & delete + fill to end code?
Kieran,
there was no offense (why try to personalize a legitimate technical question) - in a newsgroup (which this is), the responses are threaded so a conversation can be followed. The question was certainly apropro taken in the context of the full discussion - which is the context from which it was asked. I don't know what it looks like in ExcelForum, but if you can't see the history of the discussion, you could continue to waste your time duplicating answers that have already been given. http://msnews.microsoft.com/Microsof...el.Programming -- Regards, Tom Ogilvy Kieran wrote in message ... Tom, Sorry if there was any offense. I was replying to the other post http://www.excelforum.com/t177871-s where With Columns("B") was written as Columns("B").Select I can only assume that direworf pasted your code incorrectly. happy new year --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete code | Excel Discussion (Misc queries) | |||
Delete only specific fill colored rows? | Excel Worksheet Functions | |||
VBA code to fill down | Excel Discussion (Misc queries) | |||
auto fill code | Excel Discussion (Misc queries) | |||
VBA code to delete VBA code in another Workbook | Excel Programming |