1. Is there a way to delete all records below a variable row which I select highlighted ?
2. I have a spread sheet which is located in the folder xl start. How can i find this file to delete it>
How to get the contents of the currently highlighted cell into a variable and how to place it elsewhere. They suggested this....
Sub CurrentCell()
Dim CurrentCellText As String
Dim CurrentCellString As String
CurrentCellString = CStr(ActiveCell.Value)
'.......
'.......
Range("G16").Value = CurrentCellString
End Sub
My question now is - If the cell contains a runnable Dos comand, how do is send it to a Command window. (E.g. netsh interface ip address local static 192.168.0.1.255.255.255.0) (this sets your NIC's ip address).
I have manually highlighted a large magnitude of cells (I would have tried some sort of automation but there isn't really a pattern unfortunately). The cells that I did not highlight are useless to me, and I would like to get rid of them. Is there any way to delete all cells that are not highlighted on a given sheet?
View 2 Replies View Relatedi have over around 1500 pfolios in Col A, i need macro that will delete entire row which has the highlighted pfolios. Now i tried advanced filter which would work but the date in Col A6 will change every month, so dont want to go down that road, is there alternative option other than advanced filter ..
View 9 Replies View RelatedI have a worksheet in whichs Column A Cells , there is corresponding string content in Col. Z. Some cells of column Z contain of many lines (up to 100 line) which i want to reduce to one. I want to select one line through double clicking in the listbox of the userform and all other lines should be deleted and only the clicked one should remain. Code should then jump to the next non empty Z cell.
Columns B, C, D, E, F, G and H should also be shown in the userform and they should be live editable. I have attached and example file with userform and example data.
Basically what I want to do is take any value that isnt "0" in column F and place it in another column in a nicely formatted table. So basically get rid of all the zeros...
Im sure this has been done and asked before but I have no idea what to search for or if there is some easy forumla in excel for situations like this.
I have a sheet (Data) with thousands of records (transactions) of a number of different suppliers, first I have to select all the records of a specific supplier, here I'd like to use an inputbox, and copy them to another sheet.
View 9 Replies View RelatedI know this is probably very easy, but it sure has me puzzled: I have an Index & Match formula (G4) to extract the first set of data from the Sales Info, and then link (+) the Table's month & year to the drop downs, but I need the next 11 rows to automatically fill in for a 12 month summary.
View 8 Replies View RelatedI want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
I have a spreadsheet that contains multiple rows per user. I need a combo or list box that pops up asking them to select their name from the drop down list. The list would be based on column A and would only include their name once.
When they select their name, the macro would then open the Form option from the Data menu, the Criteria button would be pushed and their name entered into the form, to return the first record their name appears on, then they could forward through the records, and update using the Next record button.
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
I am new to to VB Scripting, filter the records. In the attached file there are multiple records which needs to be filtered. Once all the records are filtered, i want to delete the entire row of that record(s). I want to filter column 'F' with the values mentioned in Sheet2. I tried recording a macro, but it is not allowing me to do so as the macro has some limitations. The search and delete row loop The main purpose is to delete the row of the value, if not found, move on to next value in Sheet2.
View 4 Replies View RelatedI have a large (300K+ records) database with a sizeable amount of duplicate records. I want to delete the duplicates but this is not a matter of simply Remove Duplicates; I need to evaluate them before I do.
I am wondering what functions would:
1) select the specific record in a set of duplicates that makes a determination of a status
2) once the status has been determined for the set, delete all other records
Fields in my database:
ACCIDENT NUM (ID field, in text or General format)
DUP (for Duplicate, indicated by a character, for now its a "?")
OCC_KILLED (in Number format)
OCC_INJURED (in Number format)
SEVERITY (in text format)
Here are some scenarios:
ACC dup K I
12345 ? 0 0
12345 ? 1 2
Or:
ACC dup K I
123456 ? 0 1
123456 ? 1 0
Or:
ACC dup K I
1234567 ? 0 0
1234567 ? 0 2
1234567 ? 0 0
This is the formula for indicating if there are Duplicate records in the larger dataset:
=IF(OR(A2=A3,A2=A1),"?","")
I need to determine the Severity of the accident based on this:
If OCC_KILLED > 0 then SEVERITY = F (for Fatal)
IF OCC_INJURED > 0 and > OCC_KILLED then SEVERITY = I (for Injury)
IF OCC_KILLED and OCC_INJURED >= 0 then SEVERITY = F
IF OCC_KILLED and OCC_INJURED = 0 then SEVERITY = PDO (for Property Damage Only)
I have a code already in place for how to create the value for Severity but it DOES NOT account for duplicate records:
(in SEVERITY field):
=IF(A1<>0, "F",IF(B1<>0,"I","PDO")
sampleset.xls is a sample ot the database.
I have a spreadsheet with a column of records (text ) from B1:B4000. I would like to delete the entire row of the duplicate record. Overall data set range is (A1:I4000). This spreadsheet may contain more than 1 duplicate record. Is there in easy fix?
View 12 Replies View RelatedI'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.
Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No
E4 No
Expected results:
E1 Yes
E2 Yes
E3 Yes
E4 Yes
I have some data that looks like this.
No. Date
1519
1519 06/17/2003
1601
1652 06/30/2005
1652
1705
1705
What I would like to do is delete all numbers including those with duplicates that have a date of 2004 or sooner. In otherwords, anything from 12/31/2003 and later I want to keep, but anything from 01/01/2004 until the present.
I'm creating an Excel UserForm where the user can view, edit, and delete records they've entered.
The following code is for Deleting a selected record, and it IS WORKING. But it seems TOO SIMPLE and I want to be sure it's correct and not leaving any loose ends in the Database file that could cause corruption later on....
Sub vCLdbDel()
Dim cnt As ADODB.Connection
Dim dbPath, dbName As String
Dim stSQLAs String
Dim stCon As String
Dim dbid As Long
Run "setvars"
'Get the dbID from the selected Item in the list
With vCL.CLdbList
dbid = .List(.ListIndex, 6)
End With
'Path & FileName to the Database File
dbPath = M. Range("G2").Value
dbName = M.Range("G3").Value
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
I d like to find a method with which I can delete records from E starting from E2 that have 0 or even blanks with a macro.
View 9 Replies View Relatedhow to automatically complete with Excel the following task:
1. I have two email databases in Excel: a master database and opt-out database.
2. I need to remove from the Master database emails of opted-out people.
3. Since the databases are large, I'd like to use a relevant Excel function to do that automatically.:
In the master list (column B) I have all the emails from the Master list. I have copied in the column C of the Master list the emails of all who opted-out. I need to remove opted out emails (listed in column C) from the master email list (column B).
I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.
View 3 Replies View RelatedI d like to find a method with which I can delete records from E starting from E2 that have 0 or even blanks with a macro.
View 9 Replies View RelatedI have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
View 1 Replies View RelatedOn my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet.
To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field.
The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively.
To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
View 5 Replies View RelatedCreating a macro. Need to delete records in column D from the first blank cell. 1st blank cell variable.
View 9 Replies View RelatedI am trying to select rows that contain text using a variable counting number of rows so rNumb = count number of rows
The Problem
Worksheets("Table").Rows("2:rNumb").Copy
rNumb won't work. What is the proper syntax?
I am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code:
Sub ImportAlarms()
Dim thisSheet As Worksheet
Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet
Set targetSheet = Sheets(TARGET_SHEET)
[Code] .......
Basically I need a bit of VB to find the last cell in column F with text in it (is it End(xlUp)??) and then apply full borders from that cell up to cell Q5. A test macro tells me the border bit is as follows, so just need defining the range.
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
[Code] ......
I know how to do Range("A" & I).Select, but how about if I want columns A thru E instead?
View 7 Replies View Related