I have a worksheet and the products starts from the first row has product name and 2 following rows are other information which i do not need. i was trying to figure out a VBA code that can leave the first rwo and delete every 2 rows below the first one and then to complete this until the end of the data.
For example: in the following rows, i want to keep rows 1, 4, 7 and all others to be deleted and this process to continue until the last rows having data.
Column A
1 Product name
2 serial number
3 manufucture date
4 Product name
5 Serial number
6 manufucture date
7 Product name
8 serial number
9 manufucture date
Sub Deletesecondtworows()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
I have a normally easy task that is causing me some grief. In column f of my data I either have a number or this "____________". My goal is to delete the entire row if that line is present but my code is not finding that value in my range.
I am wondering is this some format value or something, but it shows up in the formula line as a line. Doesn't appear to be an underline, but I could be wrong. Below is my code.
trying to get macro to loop until there's no data to process.I have data in two columns A&B which it creates a chart from I then need it to skip C (which is blank) and do the same chart for columns D&E and so on indefinitely until there is no data to process..
I have attached a very simplified version of the data, the data is made up so the charts don't make sense but if you run the macro you'll see what I'm trying to achieve.
Im having a bit of an issue getting my head around why I am getting the result I am. I have data in ws1, a search list in ws2 and the results of the search in ws3. It works though for some reason it loops 3 times... not sure why.
Additionaly I want to be able to run the same sub against 2 work sheets, ie, ws1 and another ws titled "otherWS" for this example. So run the exact sub again and return the results to ws2 as well.
I have the following code that I use to create individual client files from one master spreadsheet. My problem is that it always creates and saves a blank file with only the 'rn' value in the file name. It seems that it is looping one extra time when the 'rng.value' list ends.
Sub MakeFiles() Dim wb As Workbook Dim ws As Worksheet Dim criteriaRng As Range, usedRng As Range, rng As Range Dim lh As String, ch As String, rh As String Dim rn As String
As a part of a much larger routine, I need a code to find five consequetive cells in a column with identical value "XYZ" and select the first one of them. Say,
I have a column: apples, pears, apples, oranges, xyz, oranges, xyz, xyz, xyz, apples, apples, apples, xyz, xyz, xyz, xyz, xyz, xyz, xyz, etc.
I need to select the twelfth cell in this column, highlighted red. I guess there could be some complicated if-then loop to go through the whole column, checking each cell and comparing it to the next four, but it would take forever to excecute... Is there some other way around? To find the first occurence of five xyz's one directly under another?
I have a piece of code attached to a combo box. When selected the code loops through 3, even 4 times for no apparent reason. Has anyone come across this before? The loop can start either after the final "end sub" or part way through (.clearcontents mainly) Have included the code just in case it is something obvious.
Private Sub cmbDI_GrowthBasis_Change() If UCase(Worksheets("Tables"). Range("GrowthNo")) = "NIL" Then Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = "" With Worksheets("Detail Inputs").Range("DI_GrowthRate") .ClearContents .Borders(xlEdgeLeft).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlNone .Borders(xlEdgeRight).LineStyle = xlNone .Interior.ColorIndex = 15 .Locked = False End With Else Worksheets("Detail Inputs").Range("DI_GrowthRateTitle") = Worksheets("Tables").Range("GrowthNo") & " :" End If End Sub
I have a peice of code (Below) and instead of going from work sheet 4 to the one in the workbook 2 from the end sheet by sheet running the same bit of code it just loops repeatedly on one sheet??
Sub a()
Dim n As Integer For n = 4 To ThisWorkbook.Sheets.Count - 2 Columns("W:W").Select Selection.Copy Selection.Insert Shift:=xlToRight Columns("L:L").Select Selection.Delete Shift:=xlToLeft Range("L8:L9").Select Selection.AutoFill Destination:=Range("L8:W9"), Type:=xlFillMonths Range("L8:W9").Select Application.CutCopyMode = False Next n End Sub
My following code keeps deleting leading zeros. The purpose of this code is to delete leading spaces but leave the zeros.
Code: lr = Cells(Rows.Count, 1).End(xlUp).row For Each c In ActiveSheet.Range("A2:A" & lr) If Left(c.Value, 1) = " " Then c.Value = Right(c.Value, Len(c.Value) - 1) c.Value = c.Value Next c
I have a template that when it is opened it will automatically save the file to a specific location with the date in the file name.
Currently my code is
Private Sub Workbook_Open() ActiveWorkbook.SaveAs "G:Shared"IT_New " & Format(Date, "mm-dd-yy") & ".xls", , , , , , xlShared End Sub Does anyone know a way to have this code disabled (either by deleting or making it all a comment) on the saved file (xls) so the speadsheet does not try and overwrite itself when I go back and open a previous days spreadsheet?
I've got a row of data with a column showing some sales including amounts with 0. Any row with 0 I want gone, so I want the macro to filter the list, delete all the rows with 0 in that column, and then display what's left, all the rows with some number other than 0 in that column.
The code below should filter the 0 rows, delete those rows, and then display what's left. I don't want the heading row deleted, but everything below it that is filtered. What my code below is doing now is not deleting everything it should. How can I fix it.
Sub prepmetrics() ' ' prepmetrics Macro ' for use with monthly report - don't forget the new coversheet ' Application.ScreenUpdating = False
' there may be a number of different criteria in different columns, so ' we rinse and repeat as many times as necessary but, again, each filter/delete ' task should delete all rows filtered
I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).
So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
************************************* Sub removeAllCode() Dim awi Dim awcl As Integer Dim count As Integer Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ThisWorkbook.Save Application.Quit End Sub *************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.
Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?
Range ("C8") = "= CountBlue(E4:G68)"
the code doesn't look exactly like this, I believe it is actually in R1C1 format
I am trying to write a VBA code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on.
I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.
If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.
Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.
I want to delete the contents of every cell without deleting any code from my module. The reason I ask is, I'm reading from a text file into excel.
I want to run my code to read text into excel one time through. Then i want to delete all the text, make some alterations to my code, and run the code again. That way each time the worksheet is fresh and clear before i run the code.
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code: Dim c As Range Dim SrchRng Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code: For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True Next cell
Code: For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete Next cell
I am using the following block of code, which cycles through the data and first deletes any cell with "Legal:" in it, and then cycles through again and deletes any row where the cell is blank.
The problem is that within the data, there are some locations where there are two blank rows in a row. When the code runs through, it deletes the FIRST blank row only, not the second. I Need ALL blank rows within the data set to be deleted.
Code: Sub ModifyNewData() Dim r As Range, rAll As Range Dim WS As Worksheet Dim iLast As Integer
[Code] ........
Also, if there is a way to write looking for blank rows into the first block of code looking for "Legal:" that would be cool too.
If not, just deleting all the blank rows is good. Right now, I have to have the second block of code run twice to get rid of the remaining blank rows.
Because I am pulling data from external aplication, so the 0 is fix in a row/column.. I will need to get the row/column for the whole "01" and not only "1".
Is it possible to write a cell formula (not a macro) to loop through a range of cells and test for a value? Something tells me that I need to use the "Offset" function.
