using Macro in excel spreed sheet
Dave:
I test this macro and it only delete the NA but not 0, any idea? example A B C D E 0 NA 0 NA 0 0 NA 0 NA 0 0 NA 0 NA 0 after that 0 still there, thank you. Lillian -----Original Message----- I read your other post and this'll get both 0's and NA's. Option Explicit Sub testme03() Dim myRng As Range Dim delRng As Range Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange For Each myCol In myRng.Columns If Application.CountIf(myCol, 0) = myCol.Cells.Count _ Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then If delRng Is Nothing Then Set delRng = myCol.Cells(1) Else Set delRng = Union(myCol.Cells(1), delRng) End If End If Next myCol End With If delRng Is Nothing Then 'do nothing Else delRng.EntireColumn.Delete End If End Sub <<snipped -- Dave Peterson . |
using Macro in excel spreed sheet
I used Tom's code (dated: Sat, 15 Nov 2003 20:27:36 -0500) against your data and
it wiped out all the columns. First guess why it didn't work for you: That code expects 0's, NA's or empty cells in row 1 to the last row of the worksheet. Hit ctrl-end to see if that lastrow is where you expected it. Second guess: Are those 0's the results of calculations? Could the be very small numbers that look like 0's when they appear in the worksheet (either via the numberformat of the cell or by the width of the column)? Try clicking one of them and looking at the actual value in the formula bar. Lillian wrote: Dave: I test this macro and it only delete the NA but not 0, any idea? example A B C D E 0 NA 0 NA 0 0 NA 0 NA 0 0 NA 0 NA 0 after that 0 still there, thank you. Lillian -----Original Message----- I read your other post and this'll get both 0's and NA's. Option Explicit Sub testme03() Dim myRng As Range Dim delRng As Range Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange For Each myCol In myRng.Columns If Application.CountIf(myCol, 0) = myCol.Cells.Count _ Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then If delRng Is Nothing Then Set delRng = myCol.Cells(1) Else Set delRng = Union(myCol.Cells(1), delRng) End If End If Next myCol End With If delRng Is Nothing Then 'do nothing Else delRng.EntireColumn.Delete End If End Sub <<snipped -- Dave Peterson . -- Dave Peterson |
using Macro in excel spreed sheet
Dave:
If I use my real data and it work, it wipe out O column, if I use the test data it won't work, I don't know why, as you said I hit-end, it went to last row, and I look at format cell is general. Thank you so much. Lillian -----Original Message----- I used Tom's code (dated: Sat, 15 Nov 2003 20:27:36 - 0500) against your data and it wiped out all the columns. First guess why it didn't work for you: That code expects 0's, NA's or empty cells in row 1 to the last row of the worksheet. Hit ctrl-end to see if that lastrow is where you expected it. Second guess: Are those 0's the results of calculations? Could the be very small numbers that look like 0's when they appear in the worksheet (either via the numberformat of the cell or by the width of the column)? Try clicking one of them and looking at the actual value in the formula bar. Lillian wrote: Dave: I test this macro and it only delete the NA but not 0, any idea? example A B C D E 0 NA 0 NA 0 0 NA 0 NA 0 0 NA 0 NA 0 after that 0 still there, thank you. Lillian -----Original Message----- I read your other post and this'll get both 0's and NA's. Option Explicit Sub testme03() Dim myRng As Range Dim delRng As Range Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange For Each myCol In myRng.Columns If Application.CountIf(myCol, 0) = myCol.Cells.Count _ Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then If delRng Is Nothing Then Set delRng = myCol.Cells(1) Else Set delRng = Union(myCol.Cells(1), delRng) End If End If Next myCol End With If delRng Is Nothing Then 'do nothing Else delRng.EntireColumn.Delete End If End Sub <<snipped -- Dave Peterson . -- Dave Peterson . |
using Macro in excel spreed sheet
I don't have any better guesses--sorry.
Lillian wrote: Dave: If I use my real data and it work, it wipe out O column, if I use the test data it won't work, I don't know why, as you said I hit-end, it went to last row, and I look at format cell is general. Thank you so much. Lillian -----Original Message----- I used Tom's code (dated: Sat, 15 Nov 2003 20:27:36 - 0500) against your data and it wiped out all the columns. First guess why it didn't work for you: That code expects 0's, NA's or empty cells in row 1 to the last row of the worksheet. Hit ctrl-end to see if that lastrow is where you expected it. Second guess: Are those 0's the results of calculations? Could the be very small numbers that look like 0's when they appear in the worksheet (either via the numberformat of the cell or by the width of the column)? Try clicking one of them and looking at the actual value in the formula bar. Lillian wrote: Dave: I test this macro and it only delete the NA but not 0, any idea? example A B C D E 0 NA 0 NA 0 0 NA 0 NA 0 0 NA 0 NA 0 after that 0 still there, thank you. Lillian -----Original Message----- I read your other post and this'll get both 0's and NA's. Option Explicit Sub testme03() Dim myRng As Range Dim delRng As Range Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange For Each myCol In myRng.Columns If Application.CountIf(myCol, 0) = myCol.Cells.Count _ Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then If delRng Is Nothing Then Set delRng = myCol.Cells(1) Else Set delRng = Union(myCol.Cells(1), delRng) End If End If Next myCol End With If delRng Is Nothing Then 'do nothing Else delRng.EntireColumn.Delete End If End Sub <<snipped -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
using Macro in excel spreed sheet
Dave:
That's Ok, the real data is working that is most important, you have been help me so much. thanks again. Lillian -----Original Message----- I don't have any better guesses--sorry. Lillian wrote: Dave: If I use my real data and it work, it wipe out O column, if I use the test data it won't work, I don't know why, as you said I hit-end, it went to last row, and I look at format cell is general. Thank you so much. Lillian -----Original Message----- I used Tom's code (dated: Sat, 15 Nov 2003 20:27:36 - 0500) against your data and it wiped out all the columns. First guess why it didn't work for you: That code expects 0's, NA's or empty cells in row 1 to the last row of the worksheet. Hit ctrl-end to see if that lastrow is where you expected it. Second guess: Are those 0's the results of calculations? Could the be very small numbers that look like 0's when they appear in the worksheet (either via the numberformat of the cell or by the width of the column)? Try clicking one of them and looking at the actual value in the formula bar. Lillian wrote: Dave: I test this macro and it only delete the NA but not 0, any idea? example A B C D E 0 NA 0 NA 0 0 NA 0 NA 0 0 NA 0 NA 0 after that 0 still there, thank you. Lillian -----Original Message----- I read your other post and this'll get both 0's and NA's. Option Explicit Sub testme03() Dim myRng As Range Dim delRng As Range Dim myCol As Range Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange For Each myCol In myRng.Columns If Application.CountIf(myCol, 0) = myCol.Cells.Count _ Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then If delRng Is Nothing Then Set delRng = myCol.Cells(1) Else Set delRng = Union(myCol.Cells (1), delRng) End If End If Next myCol End With If delRng Is Nothing Then 'do nothing Else delRng.EntireColumn.Delete End If End Sub <<snipped -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
using Macro in excel spreed sheet
|
using Macro in excel spreed sheet
Hi, Dave:
I have another VBA would like to ask your help. I have one excel spreed sheet, it have three worksheet, they are sheet1, sheet2, sheet3, in each worksheets on the column B each row has real long file directory, example: J:\files\docfiles\amaya01\demand.mcp.wpd, all I need is last file name: demand.mcp.wpd, it means I only need the file name after the last slash "\", all the column B has different rows of file name another example: j:\FILES\DOCFILES\Civil Service\CS-SUBIA\OLGA.SRP, all I need is OLGA.SRP file name, so can we write the macro removed everything before last slash "\". SO this macro has be in the loop for sheet1,sheet2,sheet3. thanks for the help. Lillian -----Original Message----- On top of what Tom Ogilvy wrote, I liked to lurk in these newsgroups. And when you get a few bucks to spare, you may want to get a book: Debra Dalgleish has a big ole list: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. John Green (and others) is nice, too. See if you can find them in your local bookstore and you can choose what one you like best. Lillian wrote: Dave: Thanks for all your effort, I did use the adding code you gave to me, can not tell is fast or not, anyway, thank you so much. Dave, where I can get good excel Macro on the web that way I can learn more? Lillian -----Original Message----- the easy question first. An example showing the difference between the .value and .text. Say you have 123456.323 in A1. But you have it formatted as "$#,##0.00", it'll show as: $123,456.32 So if I concatenate with .value, I lose all that nice formatting. But if I use the .text, it'll show up just like it appears in the cell. This is useful with dates, too. .value = 37931 could be formatted as a date (mm/dd/yyyy) to show 11/06/2003. (It's not really Format|Cells|Number tab|Text kind of formatting.) I just ran a test of that merge version (testme02). I put simple data in A1:D1600. It ran pretty quickly. You can speed it up by adding this to the top of the code: Dim CalcMode As Long CalcMode = Application.Calculation Application.Calculation = xlCalculationManual Application.screenupdating = false and near the bottom: Application.Calculation = CalcMode Application.ScreenUpdating = True It'll stop the flickering of the display, too (.screenupdating = false). Lillian wrote: Dave: I use that merge macro, it take a while to run, not like concatenate one, it only one second, then I got the result I need. thanks. I have one more question when you said in previous email: if your data needs to be formatted nicely, you could use: .Value = .Text _ & " " & .Offset(0, 1).Text _ & " " & .Offset(0, 2).Text how do I write the macro to formatted D E F columns to Text, before I run your merge macro script, maybe data in column D,E,F did not formatted as you said, that why take a long time to run, as you mention as above. did this formatted macro need to run separately or they can combined into merge marco? Thank you again, your are wonderful. Lillian -----Original Message----- Thanks Dave, your the greatest. I can not thank you enough. Lillian -----Original Message----- If you want to delete those columns, then you have to uncomment this line (just get rid of the leading apostrophe): '.Value = .Value It's essentially copy|paste special|values. Lillian wrote: Dave: I use the concatenate one for the test,it work out perfect, all the data is on D column, if I click D column, it is refering to =E1&" "&F1&" "&G1, if I remove the '.Range("e:g").EntireColumn.Delete again, guest what the D column become like #REF, is anyway after we run this then do somekind of pastspecial in H column with value, but with macro script? Your are a genius. thanks for all the effort. Lillian -----Original Message----- Do you mean merge like in merge cells or do you mean merge like in concatenate? I'm gonna guess concatenate. I'd insert a new column D and use a formula that did the concatenation, then convert to values, then delete the original D:F (now E:G). Option Explicit Sub testme02() Dim LastRow As Long With Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "d").End (xlUp).Row .Columns("D:D").Insert With .Range("d1:d" & LastRow) .FormulaR1C1 = "=RC[1] & "" "" & RC [2] & "" "" & RC[3]" '.Value = .Value End With .Range("e:g").EntireColumn.Hidden = True '.Range("e:g").EntireColumn.Delete End With End Sub I used the last row of column D to determine the last row to get a formula. Change this to a column that always has data. And I don't know what's in those columns, but it's not always best to concatenate and delete. As you can see it's really easy to put things together. It's not always as easy to separate them into the correct fields. It might be better to leave the formulas and just hide the columns that the formulas use. ======== Did you really mean Merge (as in Format|Cells|Aligment tab)? Then maybe something like: Option Explicit Sub testme02() Dim LastRow As Long Dim iRow As Long With Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "d").End (xlUp).Row For iRow = 1 To LastRow With .Cells(iRow, "D") .Value = .Value _ & " " & .Offset(0, 1).Value _ & " " & .Offset(0, 2).Value .Offset(0, 1).Resize(1, 2).ClearContents End With Next iRow .Range("d1:f" & LastRow).Merge across:=True End With End Sub And one last thing, if your data needs to be formatted nicely, you could use: .Value = .Text _ & " " & .Offset(0, 1).Text _ & " " & .Offset(0, 2).Text to pick up the format from the cell. Or you could specify the format you want: .Value = .Value _ & " " & format(.Offset (0, 1).Value,"mm/dd/yyyy") _ & " " & format(.Offset (0, 2).Value,"$0.00") with the Format function. Inside the worksheet, you'd use something like: =text (a1,"$0.00") Lillian wrote: Dave: Now I have all the data I need, is anyway to merge columnD,ColumnE,ColumnF together, how to write the macro to do that, I try to use excel merge cell, it did not work, because columnD,E,F has different type of data. thanks for all the help. Lillian -----Original Message----- Dave: I try yours, it work perfectly, thank you so much. Lillian -----Original Message----- I'm not sure if you got your problem solved, but modifying Ron's code slightly: Option Explicit Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows (.UsedRange.Rows.Count).Row To 1 Step -1 If Application.CountA(.Rows (r)) = 0 _ Or .Cells(r, "A").Value = "--- -" _ Or LCase(.Cells(r, "A").Value) = "problem" _ Or Trim(.Cells(r, "F").Value) = "" Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub Lillian wrote: I have one excel spreed sheet, it about 30,000 records, I need to deleted some of records, if columnsA has "problem", the record will be delete, if rows is space, the record will be delete, if ColumsA has "- --- ", the record will be delete, if column(F) has space, the record will be delete. How can I write the macro to delete those record, thanks for the help. Lillian -- Dave Peterson . . -- Dave Peterson . -- Dave Peterson . . -- Dave Peterson . -- Dave Peterson . |
All times are GMT +1. The time now is 03:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com