logo
down
shadow

Code to delete rows containing a search string skips some rows with the string


Code to delete rows containing a search string skips some rows with the string

By : Dragos Mursa
Date : November 22 2020, 04:01 AM
Any of those help I have written some code in Excel VBA below, which deletes rows based on user input. It almost completely works but in some cases leaves one or two instances of the search string in the data. , Try this code:
code :
Dim SrchStr As String

'enter email address to be searched

SrchStr = InputBox("Enter email")

'open each file and if email address is found, delete that row and save workbook

Workbooks.Open File1
Dim Cell As Variant
Dim SrchRng As range
Set SrchRng = ActiveSheet.UsedRange

lastIndex = SrchRng.Rows.Count

For i = lastIndex To 1 Step -1
    For Each Cell In SrchRng.Rows(i).Cells
        If Cell.Value = SrchStr Then
            Cell.EntireRow.Delete
            Exit For
        End If
    Next
Next

ActiveWorkbook.Close SaveChanges:=True


Share : facebook icon twitter icon
mySQL: Search multiple rows for a string, and order the results based on how often the string

mySQL: Search multiple rows for a string, and order the results based on how often the string


By : user2850030
Date : March 29 2020, 07:55 AM
To fix this issue You should really consider a Full Text search solution. Either use MyISAM tables and MySQL native full text search, or you can go the external way and use something like Sphinx fulltext search or Lucene
Any way to speed up Matlab code that has to search through 20000+ rows for a string 16x?

Any way to speed up Matlab code that has to search through 20000+ rows for a string 16x?


By : user2856641
Date : March 29 2020, 07:55 AM
this will help strcmp is often much faster than strmatch; I tried it out and it was much faster on my system, don't know exactly why (like 1000x faster, I wasn't expecting that much difference).
It returns slightly different information - strcmp returns a logical array with 1 wherever there's a match - so to get the same output as with strmatch just wrap it with a find:
code :
IndexStart=find(strcmp(get(handles.StartTime,'String'),Text)); %start time
R For loop delete range of rows from one string to a second string in a column

R For loop delete range of rows from one string to a second string in a column


By : rishi kaithwas
Date : March 29 2020, 07:55 AM
it fixes the issue You can try something like this, by constructing the index to be removed using the Map function:
code :
indexToRemove <- unlist(Map(`:`, which(df$`Column 1` == "StringA"), 
                                 which(df$`Column 1` == "StringB")))

df[-indexToRemove, ]
   Column 1 Column 2
6  unknownK        6
7  unknownR        1
12 unknownT        9
structure(list(`Column 1` = structure(c(1L, 3L, 4L, 8L, 2L, 5L, 
7L, 1L, 10L, 6L, 2L, 9L), .Label = c("StringA", "StringB", "unknownC", 
"unknownD", "unknownK", "unknownP", "unknownR", "unknownS", "unknownT", 
"unknownU"), class = "factor"), `Column 2` = c(1L, 9L, 11L, 5L, 
7L, 6L, 1L, 76L, 2L, 41L, 3L, 9L)), .Names = c("Column 1", "Column 2"
), class = "data.frame", row.names = c(NA, -12L))
Delete rows if rows (not columns separately) contain a string

Delete rows if rows (not columns separately) contain a string


By : Rakshith
Date : March 29 2020, 07:55 AM
I wish this help you I import data from a CSV where I am replacing the empty fields with an 'EMPTYFIELD' value. , One way is to use np.logical_or.reduce. Here is an example:
code :
import pandas as pd, numpy as np

df = pd.DataFrame([['A', 'B', 'C', 'D'],
                   ['E', 'F', 'G', 'H'],
                   ['G', 'A', 'D', 'I'],
                   ['L', 'K', 'A', 'J'],
                   ['S', 'T', 'U', 'V']],
                  columns=['COL1', 'COL2', 'COL3' ,'COL4'])

df[~np.logical_or.reduce([df[col].astype(str).str.contains('A') for col in df])]

#   COL1 COL2 COL3 COL4
# 1    E    F    G    H
# 4    S    T    U    V
Delete rows containing a specific string while keeping rows where the cell is blank

Delete rows containing a specific string while keeping rows where the cell is blank


By : user3267742
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , When deleting inside a loop, it is always better to do it backwards, like this:
code :
For x = LastRow To 2 Step -1
    ...
Next x
Related Posts Related Posts :
  • Delete a UserForm and add a New UserForm in Excel VBA
  • Keypress about not allowing testers inputing 2 digits in a textbox in vba
  • VBA listobject lookup function fast
  • Excel VBA For Loop to Duplicate Data
  • How do I change the background image of a chart in vba?
  • VBA - Loop through files in a folder AND copy single cells as well as a range, if condition is met
  • Screenshotting Google Maps and Pasting into Excel Document VBA
  • Comments split by page break
  • Data Manipulation / Dissecting
  • Convert multi column table to single columns with repeat pattern
  • IF statement to trigger specific conditional formatting?
  • Offsetting a date based on time in excel using if and function
  • Search a copied value MACRO
  • Running an Excel macro from another workbook
  • Open file in vba Excel environment
  • How can i count the frequency of those of a number showing from a countif but only once?
  • Excel - check values in multiple columns and return the sum of a third column
  • VBA userform: application-defined or object-defined error
  • Delete without warning any sheet for which the name contains the word "sheet"
  • Csv file is not aligned correctly in excel
  • Filter one column and count values in other
  • vba excel listobject HeaderRowRange
  • how to add text box/shape around each cell in the selection?
  • Determining Last Names in excel from a range of full names in varying formats
  • Read Column A, insert rows based on pattern
  • If 1 or both columns has word, then enter text
  • SUM of VLOOKUP functions meeting criteria in Excel
  • Extract text using css selector
  • How to use Excel::Writer::XLSX to create a table where inserting a row copies a column's formula?
  • Change Chart Colours (bar) Based on Source Cell (using conditional formatting and VBA)
  • Web query throws error: import currenturl matches no exports. Did you miss a module reference?
  • VLookUp value to another worksheet
  • Writing a loop that has multiple criteria
  • Paste a copied data range in all open workbooks using a loop
  • Use Excel-VBA to colour a range y if value is certain number is placed AND colour range x if value is certain number is
  • Getting meta property from a URL
  • Power Query - Create date from datenumber and time column
  • Colour a cell if a certain number is entered
  • vba target.count=1 error when selecting all
  • Excel selecting special condition
  • Searching through list of random files in directory and renaming files based on Cell Value
  • Avoid counting unfiltered results using COUNTIFS formula
  • Excel macro saving sheet as pdf to sharepoint to location dependent on input in new folder
  • Power Query - replacing text in a new column
  • Check availability of folder/files
  • Souble Loop sheets and cells
  • getting compiler error (invalid identifier)
  • Excel Formula - Match substrings of List to List
  • Trigger same macro with different command button with Button properties
  • Laravel Export Excel doesn't return an excel file
  • Put timestamp when a checkbox is ticked or unticked
  • Index was out of range must be less than or negative
  • VBA Saving delay after data entry
  • Sort cells that have the same date but different time
  • VBA - Delete all rows that do not contain at least 1 highlighted cell
  • Access password blocked sheet - Excel
  • Excel not displaying text when html:color applied via XSLT
  • VBA How to programmatically use conditional formatting and have it apply to numbers only
  • SUM lots of half hourly data into a separate list
  • OpenXML Pagesetup (PrintOptions, pageMargins, Orientation, headerfooter) Error
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org