logo
down
shadow

How to keep deleting cells based on their value


How to keep deleting cells based on their value

Content Index :

How to keep deleting cells based on their value
Tag : google-apps-script , By : user176445
Date : November 24 2020, 12:01 PM

may help you . i wanted to keep deleting cell B1 if there is no value on it. , Try while instead of if:
  var range, Question1;
  Question1 = sheet.getRange('B1').getValue();
  while (Question1 == '' ){
    range = sheet.getRange("B1");
    range.deleteCells(SpreadsheetApp.Dimension.COLUMNS);    
    Question1 = sheet.getRange('B1').getValue();
 }

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Deleting cells based on conditions


Tag : excel , By : cnemelka
Date : March 29 2020, 07:55 AM
I wish this help you I'm more hungry for reputation than Ben M here so I'll send you some code. :) You should still take his advice of course, and start reading good books.
The following could use some fine-tuning still, but should be a good starting point. If, as you wrote, you want Excel to automatically move the Employee Name and Status as soon as the Inactive choice is made, this should do the trick:
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    ' Only react to edits in Column B:  '
    If Not Intersect(Target, Sheets("Employee List").Range("B:B")) Is Nothing Then

        ' Dont do anything if > 1 cell was just changed:   '
        If Target.Cells.Count = 1 Then

            ' Only make the change if the new value in Col B is "inactive":    '
            If Target.Value = "Inactive" Then

                ' Find the next available cell on the Misc sheet for a name:   '
                Dim nextRange As Range
                Set nextRange = Sheets("Misc").Range("A65536").End(xlUp).Offset(1, 0)

                ' Cut the employee name and status and paste onto the Misc sheet:   '
                Range(Target, Target.Offset(0, -1)).Cut
                Sheets("Misc").Paste       Destination:=Sheets("Misc").Range(nextRange.Address)

            End If
        End If

    End If


    Application.EnableEvents = True

End Sub

Algorithm for inserting/deleting cells in UITableView based on Core Data object?


Tag : development , By : baylisscg
Date : March 29 2020, 07:55 AM
will be helpful for those in need Here is the working code, making heavy use of sets. If you know of a way to simplify it, don't hesitate to speak up :)
[self.tableView beginUpdates];

NSMutableArray *preIngredients = [NSMutableArray arrayWithArray:[recipe orderedIngredients]];
[self.recipe.managedObjectContext rollback];
NSMutableArray *postIngredients = [NSMutableArray arrayWithArray:[recipe orderedIngredients]];

NSMutableSet *beforeIngredients = [NSMutableSet setWithArray:preIngredients];
NSMutableSet *afterIngredients = [NSMutableSet setWithArray:postIngredients];

NSMutableSet *ingredientsToRestore = [NSMutableSet setWithSet:afterIngredients];
[ingredientsToRestore minusSet:beforeIngredients];

NSMutableSet *ingredientsToRemove = [NSMutableSet setWithSet:beforeIngredients];
[ingredientsToRemove minusSet:afterIngredients];

NSMutableSet *indexPathsToRestore = [NSMutableSet setWithCapacity:[ingredientsToRestore count]];
NSMutableSet *indexPathsToRemove = [NSMutableSet setWithCapacity:[ingredientsToRemove count]];

for (Ingredient *ingredient in ingredientsToRemove)
    [indexPathsToRemove addObject:[NSIndexPath indexPathForRow:[preIngredients indexOfObject:ingredient] inSection:0]];

// Also remove the "add new ingredient" row
[indexPathsToRemove addObject:[NSIndexPath indexPathForRow:[preIngredients count] inSection:0]];

for (Ingredient *ingredient in ingredientsToRestore)
    [indexPathsToRestore addObject:[NSIndexPath indexPathForRow:[postIngredients indexOfObject:ingredient] inSection:0]];

NSMutableSet *commonIndexPaths = [NSMutableSet setWithSet:indexPathsToRemove];
[commonIndexPaths intersectSet:indexPathsToRestore];

[indexPathsToRemove minusSet:commonIndexPaths];
[indexPathsToRestore minusSet:commonIndexPaths];

[self.tableView insertRowsAtIndexPaths:[indexPathsToRestore allObjects] withRowAnimation:UITableViewRowAnimationTop];
[self.tableView deleteRowsAtIndexPaths:[indexPathsToRemove allObjects] withRowAnimation:UITableViewRowAnimationTop];

[self.tableView endUpdates];

Deleting Excel cells based on a text string (current code not working)


Tag : excel , By : xie renhui
Date : March 29 2020, 07:55 AM
around this issue I am trying to remove all cells in my spreadsheet that have the word TOTAL in them. My current VBA code: , Based upon what we discussed above, here's what you're looking for:
  Sub Delete_Rows()      
  Dim RNG As Range, cell As Range, del As Range      
  Set RNG = Intersect(Range("A1:A5000"), ActiveSheet.UsedRange)

     For Each cell In RNG
        If InStr(1, UCase(cell.Value), "TOTAL") > 0 Then
           If del Is Nothing Then
              Set del = cell
           Else
              Set del = Union(del, cell)
           End If
        End If
     Next cell

  On Error Resume Next
  del.EntireRow.Delete

  End Sub

Deleting rows based on duplicate cells and the content of a second column (VBA)


Tag : excel , By : hammer_1968
Date : March 29 2020, 07:55 AM
hope this fix your issue I believe this is nearly there - MAKE SURE TO TAKE A BACKUP OF YOUR DATA BEFORE RUNNING asthis will overwrite the data
Sub test()
Dim IN_arr()
Dim OUT_arr()

IN_arr = ActiveSheet.UsedRange.Value2
Count = 1
ReDim OUT_arr(UBound(IN_arr, 2) - 1, Count)
Found = 1

For i = 1 To UBound(IN_arr, 1)
    Found = 1
    For c = 1 To UBound(IN_arr, 1)
        Comp1 = Right(IN_arr(i, 2), Len(IN_arr(i, 2)) - InStr(1, IN_arr(i, 2), "S_LA") - 3) 'Compare last section
        Comp2 = Right(IN_arr(c, 2), Len(IN_arr(c, 2)) - InStr(1, IN_arr(c, 2), "S_ZS") - 3)

        Comp3 = IN_arr(i, 1) 'Compare first section
        Comp4 = IN_arr(c, 1)

        If Comp1 = Comp2 And i <> c And Comp3 = Comp4 Then
            Found = 0
        End If
    Next
    If Found = 0 Then
        'do not keep row
    Else
        'keep row
        If OUT_arr(UBound(IN_arr, 2) - 1, Count - 1) <> "" Then
            Count = Count + 1
            ReDim Preserve OUT_arr(UBound(IN_arr, 2) - 1, Count)
        End If

        For cols = 0 To UBound(IN_arr, 2) - 1
            OUT_arr(cols, Count - 1) = IN_arr(i, cols + 1)
        Next


    End If
Next

ActiveSheet.UsedRange.ClearContents
ActiveSheet.Range("A1").Resize(Count, UBound(OUT_arr, 1) + 1).Value = Application.Transpose(OUT_arr)

End Sub

Deleting rows based on values in multiple cells per row


Tag : excel , By : nhuser
Date : March 29 2020, 07:55 AM
I hope this helps . I don't see any advantage in copying data to array and looping array after to check values.
Also i'd be carefull using integer type as it's only -32 768 to 32 767. Long is safer to use i think.
When you refer to cells/ranges use workbook/sheet to point directly where you want.
This for example Cells(Rows.Count, 11).End(xlUp).Row refers to currently active sheet and that might be not intended.

So here is my version
Option Explicit

Sub DeleteRowtest()
    Dim LastRow As Long, i As Long
    Dim Found As Boolean
    Dim Ws As Worksheet
    Dim aCell As Range

    'turn off screen updating and sheet calculation to improve speed
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set Ws = ThisWorkbook.Sheets("Your sheet name") 'set reference to sheet

    With Ws 'all references starting with "." from now on will refer to sheet "Ws"
        LastRow = .Cells(.Rows.Count, 11).End(xlUp).Row 'Find last row

        For i = LastRow To 2 Step -1 'loop rows from last to 2
            Found = False 'reset variable
            For Each aCell In .Range(.Cells(i, 5), .Cells(i, 10))
                If aCell.Value > 0 Then ' this will be true also for text in cells
                'if you want to check for numeric values only you can try this
            '   If aCell.Value > 0 And IsNumeric(aCell.Value) Then
                    Found = True
                    Exit For
                End If
            Next aCell
            If Not Found Then 'no positive value found in row
                .Rows(i & ":" & i).EntireRow.Delete
            End If
        Next i
        .Range("B2").Select
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
Related Posts Related QUESTIONS :
  • How to make a form using Google Sheets script editor
  • How to get older versions of Google Spreadsheet data?
  • How to use scope "https://www.googleapis.com/auth/script.webapp.deploy"
  • Get a Cell value as value for function
  • Google Script ModalDialog/ ModelessDialog setWidth and setHeight feature not working
  • How to send a custom message when adding a viewer to Google Spreadsheet via Google Apps Scripts?
  • Looping in Creating PDF in Google Sheet
  • Is it possible to create a gmail label and choose its color using Google Apps Script?
  • Can I protect cells (user not allowed to edit) and run script that makes changes in these cells (e.g. hide row, unhides
  • Google Sheets custom sidebar: How to display a current cell value in sidebar without reload the sidebar?
  • oAuth disabled on google script
  • getOAuthToken returns null when called from Google Sheets
  • Use google-apps script url in project html
  • How to adjust as custom size margins and paper size in script to save google spreadsheet in PDF?
  • Google Apps Script HtmlServices template variables and global variables
  • What's the difference between a Page and a Slide in Google Slides?
  • How insert date from cell to google script?
  • Google Forms Add-On - menu item not appearing on initial load after installing plugin
  • How can I modify this function to use multiple tabs and columns for each tab?
  • Efficient way to check if an array of cells is not the desired value
  • Google sheet script starting from the 1000th line?
  • Google Script logger not showing what sheet edit was made on from event data
  • Is there a better way to make a copy of a spreadsheet with content only?
  • Increment column of cells by 1 if a condition is met
  • Need to delete an entire row based on one cell's criteria
  • Adding a few members to a Google Group in 1 URLFetchApp Call?
  • Referencing column heading text in form response spreadsheet in app script code
  • How can i set the correct answer to a question with AppScript?
  • Need help finding the maximum value in a column in Google Sheets using Google Apps Script
  • Creating array with change log data and sending it via email
  • Exit script if Google Sheet not opened or not modified today (within 24 hours)
  • Input name and check for existence of spreadsheet
  • Google sheets script for changing reference
  • Function onOpen not activating when i open my forms
  • Google Apps Script withSuccessHandler is running before function fully executes
  • Google App Script - Make numeric entries 0 if conditions in another column match
  • Get a Complete Score from Spreadsheet
  • setValue function executes, but values not updating
  • Google Forms response dashboard
  • How to allow all/ any extension files to upload in Google Drive
  • How to print a drive document with Google scripts
  • Send a mail with pdf attachment with help of Google script
  • Some questions about Google Data Studio and Google Apps Script
  • Google Apps Script - Sheets - IF statement with INCLUDES
  • google apps script - paste in next empty row
  • can i call time based trigger on gmail add-ons?
  • Return 2D array from Google Apps Script
  • Google Script to Clear multiple ranges.
  • Periodically refresh IMPORTXML() spreadsheet function
  • google-apps script user must give permission on OWN drive to open spreadsheet in app-server's drive?
  • Google Script pulling dynamic HTML iFrame from Google Sheet Cell
  • Approval button in mail from AppScript
  • Invalid BACKGROUND_COLOR?
  • Object logs null instead of calendar data
  • Splitting and Trimming Columns in Google Sheets Script?
  • Is it possible to create time driven triggers in Google Sheet Add On?
  • Trying to cross-check two ranges in Google Sheets with a script
  • Is it possible to "appendListItem" starting from a specific number?
  • How to check if a calendar event has been deleted when iterating through an array of id's in Google Sheets?
  • How to format the resource for Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId) to create a column group
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com