VBA (Excel) - Delete a row Solved

Question

Hello!

I would like to know how to search for a value in a column and delete the corresponding row...
Here is what I did:

 Range("A1").Select Do While ActiveCell <> "toto" ActiveCell.Offset(1, 0).Select Loop Selection.Delete Shift:=xlUp

Actually, I can't select the entire row to delete it...

Does anyone have a solution?... Thank you in advance! ;o)

Mangda · Accepted Answer

And simply a

Selection.EntireRow.Delete

Wouldn't that be easier?
Or a bit more complicated, but simpler than what was proposed:

Rows(Selection.Row).Delete shift:=xlUp

But I see at least two problems with your code:

If "toto" appears twice, it will only be deleted once
If "toto" never appears, you will get an error on the offset at line 65536.

Not knowing how many rows you have, you could use the following code:

For i = Cells(1, 1).CurrentRegion.Rows.Count To 1 Step -1 If Cells(i, 1).Value = "toto" Then Cells(i, 1).EntireRow.Delete Next

knowing that you can replace Cells(1, 1).CurrentRegion.Rows.Count with the corresponding number of rows.

DaNot · Answer

Hello,  Try: Range ("A:A").Select Selection.Delete ...  DaNot

DaNot · Answer

Ah, ok...

Here is a little function that might be useful to you:

 Function GetColumn(aValue As Long) As String If aValue < 26 Then GetColumn = Chr(64 + aValue) Else GetColumn = Chr(64 + (aValue \ 26)) & GetColumn(aValue Mod 26) End If End Function

This function returns the column associated with a number (e.g. 49 returns "AW", 55 returns "BC"). All you have to do is:

Range (GetColumn (ActiveCell.Column) & ":" & GetColumn (ActiveCell.Column)).Select Selection.Delete....

DaNot

DaNot · Answer

It's simpler than I thought ;-)

There is the Row property of a Range object that contains the row number. So, it just takes using

Range(Trim(Str(ActiveCell.Row)) & ":" & Trim(Str(ActiveCell.Row))).Select

voulou...

DaNot

XRay · Answer

Indeed, it's simpler and it works just as well! As for duplicates, I rely on a unique code, so there's no problem with that, but thank you for your advice!

seb · Answer

Are you trying to delete a row in your Excel table??? But is that row present multiple times in your table?

scafandre · Answer

Hello  I would like to delete an entire line that does not start with a number... is that possible?   Thank you in advance.

MrB · Answer

And I  am looking to delete the rows where the first 4 cells are empty...  Looking forward to hearing from you, thank you in advance.

poutz · Answer

Bonjour
I have a counting program to determine entrants and exitants per minute
I would like it to be every 10 minutes

Here is the code:
Public SourceSheet As String

Sub CountingProcess() Dim StartTime As Date Dim CountingStart As Boolean 'enter the first time noted in a counting campaign StartTime = InputBox("Start time of counting campaign", "time initialization") 'Generation of a sheet for each key SourceSheet = ActiveSheet.Name For i = 1 To 4 Sheets.Add ActiveSheet.Name = "T" & i Next 'Generation of Summary Sheet Sheets.Add ActiveSheet.Name = "Summary" For i = 1 To 4 Range(Chr(i + 66) & 1).Value = "T" & i Next i 'Distribution of data in the corresponding key sheet Sheets(SourceSheet).Select i = 1 Do While Sheets(SourceSheet).Range("A" & i).Value <> "" CellValue = Sheets(SourceSheet).Range("A" & i).Value Sheets(CellValue).Range("A" & i).Value = CellValue Sheets(CellValue).Range("B" & i).Value = Sheets(SourceSheet).Range("B" & i) Sheets(CellValue).Range("C" & i).Value = Sheets(SourceSheet).Range("C" & i) Sheets(CellValue).Range("D" & i).Value = Sheets(SourceSheet).Range("D" & i) Sheets(CellValue).Range("E" & i).Value = Sheets(SourceSheet).Range("E" & i) i = i + 1 Loop 'Sorting sheets For i = 1 To 4 Sheets("T" & i).Select Columns("A:G").Select Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Next i 'Insertion of a column containing information of the nth minute since the start time For i = 1 To 4 Sheets("T" & i).Select j = 1 Do While Range("A" & j).Value <> "" Range("G" & j).Value = 1 + Minute(Range("E" & j).Value - StartTime) + 60 * Hour(Range("E" & j).Value - StartTime) j = j + 1 Loop If j > 1 Then 'recording the number of minutes between start and end of counting 'and the number of lines for the sheet Range("F1").Value = Range("G" & j - 1) Range("F2").Value = j - 1 End If Next i 'Filling in the summary sheet the grouping columns indicating the number of counts 'for each elapsed minute and each key For i = 1 To 4 Sheets("T" & i).Select Sheets("Summary").Range(Chr(i + 66) & 2).Value = Range("B1").Value k = 1 LastCountedRow = 1 For j = 1 To Range("F1").Value 'number of minutes Sheets("Summary").Range("A" & j + 2).Value = j 'current minute Sheets("Summary").Range("B" & j + 2).Value = DateAdd("n", j - 1, StartTime) 'current time counting = 0 CountingStart = False For k = LastCountedRow To Range("F2").Value 'number of lines If Range("G" & k) = j Then CountingStart = True counting = counting + 1 End If If CountingStart And Range("G" & k) <> j Then LastCountedRow = k 'memorization for entering the next loop Exit For End If Next k Sheets("Summary").Range(Chr(i + 66) & j + 2).Value = counting 'indication of the number of counts for the current minute Next j Next i End Sub

Thank you very much for your help

Felipe · Answer

DaNot, the function you provided was very helpful to me, but there is just one problem:
When aValue=0, it bugs...

So for those who will still use this function, here is a small adjustment to DaNot's function:

---

Function GetColumn(aValue As Long) As String If aValue <> 0 Then If aValue < 26 Then GetColumn = Chr(64 + aValue) Else GetColumn = Chr(64 + (Int(aValue \ 26))) & GetColumn(aValue Mod 26) End If Else: Exit Function End If End Function

---

Felipe

isabelle · Answer

Hello,

how can we delete a word that appears 2 times or more in Excel so that it completely disappears and only keep the unique words that have no duplicates in the column?

Thank you for your response

Isabelle

Lidy · Answer

Bonjour,

I have a problem with VBA. Can you help me?

I have data in Excel, and I have a column F that contains the codes, so I wanted to create a loop that searches in column F, and when it finds the code "NOT", it deletes the entire row corresponding to that code.

Here is what I tried, but unfortunately, it doesn't work:

Range("F2").Select ActiveCell.Offset(-1, 0).Select Do While ActiveCell.Value <> "NOT" If ActiveCell.Value = "NOT" Then Selection.EntireRow.Delete End If Loop

Thank you.

Ed. · Answer

ActiveCell.EntireRow.Delete 'for a row ActiveCell.EntireColumn.Delete 'for a column

dlescot · Answer

With a delay....  ' delete the entire line Dim range As Range, s As Range Set range = Range("A1").EntireRow For Each s In range s.Delete Shift:=xlUp Next s

VBA (Excel) - Delete a row

14 answers

Impossible internet connections

E.tre456.worm.windows

Cursor issue on word doc

How to activate my facebook account deactivate

Driving at 110 km/h on the highway: fuel economy and safety gains

Canon pixma printer not detected via livebox 7

Windows security

Samsung smart switch app

Photo sorting software

Pc audio connection: jack ports, hi-fi chain