modifying this code to apply to several sheets in the workbook that I specify rather than just the active sheet. Below is the current code.
Sub DeleteAllExceptFor()
'This will delete all row except for the one you specify to exclude.
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim strKeep As String
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
strKeep = .InputBox("What Value should be excluded?")
End With
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
I have an formula if statement that returns "deletethisrow" if the test is true.
For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.
I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.
Sub Dupe_Killer() Dim str As String Dim str2 As String Dim c As Integer Dim i As Integer Application. ScreenUpdating = False Application.Calculation = xlCalculationManual Sheets("SAMPLE").Select rw = Cells(2, 1).End(xlDown).Row 'Sort Data by Date, Location & Number Range(Cells(1, 1), Cells(1, 14)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _ , Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
I have sheet 1 containing S.NO, Name and amt. sheet 2 - will display S.No from sheet 1 automatically..but the probelm is if i try to insert a new row (in sheet 1) in between its not displaying in sheet 2. Similarly when i delete a row in sheet 1 its showing as # ref in sheet 2. How to correct these. Do i need to write any macros.
I need a code that will find "Grand Total" in column H and then select the row that is 2 above it and delete so if "Grand Total is in H61 it will delete row 59. the cell references can change depending on the report. is this possible?
S.N. NAME OF ITEMS MRP PACKING SIZE DISCOUNT (%) VAT RATES (%) ORDER QTY IN PCS ORDER QTY IN BOXES NET PRICE FOR PCS TOTAL PRICE COPY CODE
1 Aamlki Rasayan - 100 grm. 65 200 20.63 5 150
[Code] ........
I want to delete rows contains no value. Eg. Column H is empty. I want to delete entire column not contain value. Or column G some rows not contain value, how to delete with macro.
I want to delete the item in the row by red on condition If there are cell.value= C and cell.offset(0,-1).value= 33, then place all the cells in rows below in place of deleted cells (red).
Row A B C D E F -------------------------- 6 - 11 a 1 1 1 1 7 - 22 b 2 2 2 2 8 - 33 c 3 3 3 3 9 - 44 d 4 4 4 4
------------------------
The result of the Row A B C D E F ----------------------------- 6 - 11 a 1 1 1 1 7 - 22 b 2 2 2 2 8 - 44 d 4 4 4 4
I have some weight scales which i can connect to the pc and download some values into an excel spreadsheet. trouble is in the date column the values in each cell contain a ' before the time & date = '08:50:30 27/04/2008
I want to create a graph which contains the date but in a correct format being mmm/yy
Instead of selecting each cell and removing this ' before the value after each time i connect and download a bulk of data - is there a way e.g vba to remove this ' and convert into a format of mmm/yy
how to add or delete a row based on certain criteria. The criteria would be Customer and CustSet. Whenever the workbook is opened I need to run a check on Customer and CustSet to see if those exist. If those exist in the record then just move to the next record, but if the do not then delete that row. Also, if a new Customer and CustSet is found I need to add a new row. Customer and CustSet are what make each record unique. So, you should never have the same Customer and CustSet for 2 records.
Public Sub MRASScheduleExport() Dim rsEPN As DAO.Recordset Dim rsCalc As DAO.Recordset Dim db As Database Dim strCalc As String Dim rngCalc As Excel. Range Dim objWkb As Excel.Workbook Dim objSht As Excel.Worksheet Dim sngsht As Single Dim dbfield As Field, qdf As QueryDef Dim iRow As Integer Dim dtPlanned As Date Dim bolMod As Boolean, bolSpare As Boolean Dim strFile As String, LCell As String, LColorCells As String Dim sngCustomer As Single, sngCustSet As Single Dim rsCol As DAO.Recordset 'Open Workbook strFile = GetOpenFile_TSB(CurrentProject.Path, "Fan Reverser and Kit Schedule", "*.xls") Set objXL = New Excel.APPLICATION If strFile = "" Then: Exit Sub Set objWkb = objXL.Workbooks.Open(strFile) Set db = CurrentDb
bolMod = True objXL.Visible = True 'loop through workbook set activesheet For sngwrk = 1 To objWkb.Worksheets.Count Set objSht = objWkb.Worksheets(sngwrk) strqry = "vwMRSScheduleEPN" & objSht.Name........................
ado sql delete rs when i rs.delete, i get an error message. "Error message—current recordset doesn’t support updating. This may be a limitation of the provider…”
'grab jobs from "que" holding...xls... Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long strSourceFile = "\03-serverdatadatabaseHolding_Schedule_RG.xls" strsql = "SELECT * FROM [Jobs$];" 'connection... Set cn = New ADODB.Connection 'On Error Resume Next 'open connection..................
this code continues on like this untill row 118 and deletes all the rows in between. It works ,but it's a bit long, If someone can show me how to write a loop to shorten it.
I am trying to write a macro to delete every other row of data in a massive set of data that approaches 60,000 rows. So far everything I've tried has caused overload in Excel. The simpler For-Next loops that I write take forever - well, at least 10 minutes.
Sub Del_Every_Other_Row() For j = 2000 To 60000 Step 2000 Set del_range = Range(Cells(j - 1998, 1), Cells(j - 1998, 20)) For i = j - 1996 To j Step 2 Range(Cells(i, 1), Cells(i, 20)).Select Set del_range = Union(Selection, del_range) Next i del_range.Delete Shift:=xlUp Next j End Sub
I am trying to locate a VBA code which can delete rows based on something similar to the COUNT function. For example data in rows 1-15 are for store #1234 and data in rows 16-30 are for store #4321 and so on, where the top 15 items are listed for each store. There are 9 columns containing data. Data has been sorted by column 9. I want to keep the top 5 rows for each store and delete out the remaining 10 (for each store). I am sure there is something already out here, I just haven't run across it yet.
I have a .csv with tons of data, and I need to delete every third row to get the total number of rows down to 32000 - so I can plot them. Up until recently I was using the following code to do this:
In order to save time I'd like to incorporate Do Until somehow, so that when the lastrow = 32000, the deletion process stops. I tried the following, to no avail (it ran, but didn't stop at 32000):
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row() Do Until lastrow = 32000
I've attached a test excel to this post. Column C contains several identical email addresses. I need ALL the rows containing the duplicate data removed, not leaving one remaining as Remove Duplicate Values normally does.
I'm going to be doing this with many Excels all with differing amounts of data surrounding column C. I need a reliable way to achieve this, regardless of how many extra data columns exist. If there is a duplicate of data in column C, all rows containing that data need to go, regardless of what other data may also be present in those rows.
But I only want the first 2 digits (I need the last two digits deleted). I don't want to just divide by 1000 as this will leve me with a decimal. The numbers are in text format as some of them begin with a 0. So it would be:
I am working with a spreadsheet created by a predecessor and there is a picture at the top of one of the worksheets. When I select the picture and hit Delete, it seems to work, but the picture is still there. When I move it around the worksheet and resize it, a copy with the same name ("Picture 47") remains in the spot I moved it from. When I delete the resized/moved picture, it deletes, but the picture still remains in the original spot.
When I record a macro and select the picture and hit delete - or even when I move the picture and resize it - the recorded macro is blank.
I'm having a problem with one of my spreadsheets. Back when I initially created it, I copied and pasted some information from another (web-based) program, including a check box that was at the end of the data. For some reason, this check box does not behave like the ones that you insert from excel itself: I can't select it at all. it seems to be somewhat attached to the cells it's near, in that if I insert cells to its left, it moves over. However, that is the only way I have been able to affect it so far, and I cannot for the life of me figure out how to delete it. It's not really disrupting anything, but it's a nuisance, and it's not very professional-looking.
