Check column and delete each row if a cell value = a string or it is empty

T

TitoElan

New Member
Joined
Jun 10, 2022
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to edit or make some old vba code work.
After copying a Range from one workbook and pasting them into another (and it works) that part of the vba code should check a column and if any cell contains a certain string or is empty, the row should be deleted.
With the empty cells the code are working and deleting the rows. With the strings not. But I dont even understand how it is working.

The variable for searching the strings is the
"KeinProfil =...."
Thats what is not working

With the line " this = UCase(Trim(Selection.Cells(1, 2).Value & Selection.Cells(1, 3).Value))" the code is finding correctly the empty cells. But thats what I dont understand.
The code runs till the end with no errors.

I've been trying to fix it but couldn't find the solution :/

VBA Code: 
    last = UCase(Trim(Range("B6").Value & Range("C6").Value))
GewichtAnfang = "F6"
LaengeAnfang = "G6"
KeinProfil = "$Gesamtstückzahl$Länge FW-Abschnitt [m]$Nettogewicht FW$Neigung(ja=1):"
KeinProfil = KeinProfil & "$Endstirnplatten (0=gelenkig; 1=biegesteif):$Länge FW-Träger [m]"
KeinProfil = KeinProfil & "$Beanspruchungsgruppe$Eingabe: ja/nein$Satteldach$auslegen+messen"
KeinProfil = KeinProfil & "$nur Montageaufwand, kein Materialpreis$HM28x15, l=100mm$22x175"
KeinProfil = KeinProfil & "$Materialdicke/Umfang$Fl35x25$%"
KeinProfil = KeinProfil & "$"
For i = 6 To 5000
   iRow = i & ":" & i
   Rows(iRow).Select
   this = UCase(Trim(Selection.Cells(1, 2).Value & Selection.Cells(1, 3).Value))
   If InStr(KeinProfil, "$" & this & "$") > 0 Then
      Rows(iRow).Delete
      last = this
      i = i - 1
   Else
      If this = "" Then
         Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)
        'If MsgBox("Jetzt wird gelöscht", vbOKCancel) = vbCancel Then Stop
         Loeschbereich = i & ":5000"
         Rows(Loeschbereich).Delete
         i = 5000
      Else
         If this <> last Then
            Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)
            GewichtAnfang = "F" & i
            LaengeAnfang = "G" & i
           'If MsgBox(last, vbOKCancel) = vbCancel Then Stop
         End If
        last = this
      End If
   End If
Next
 
Sort by date Sort by votes
Alex Blakenburg said:
This looks like a very dangerous piece of code to me. What are you trying to do with this ?
VBA Code: 
      If this = "" Then
         Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)
        'If MsgBox("Jetzt wird gel�scht", vbOKCancel) = vbCancel Then Stop
         Loeschbereich = i & ":5000"
         Rows(Loeschbereich).Delete
         i = 5000
Click to expand...
I have an excel file where I calculate the cost of a manufacturing. The macro creates a new workbook, then copies a range from my excel file, pastes it into the new workbook and after that deletes the entries I don't need and sorts the rest. It also adds an empty row between each sorted rows.

To check what I don't need, I select a column and check the values of the cells in that column. There are some cases where I delete the row.
1. If the cell from the checked column is empty but the row it isnt. For this I use the part of the code you are asking for. And at the moment only that part is working.
2. All cases when the cell contains the following strings. Any of them. And with the first code I posted that's the part that doesn't work. I get no errors, the macro runs but the rows doesnt get deleted. After the copy-paste the strings are looking a little weird because of the letters "ä,ö,ü"
VBA Code: 
KeinProfil = "$Gesamtst�ckzahl$L�nge FW-Abschnitt [m]$Nettogewicht FW$Neigung(ja=1):"
KeinProfil = KeinProfil & "$Endstirnplatten (0=gelenkig; 1=biegesteif):$L�nge FW-Tr�ger [m]"
KeinProfil = KeinProfil & "$Beanspruchungsgruppe$Eingabe: ja/nein$Satteldach$auslegen+messen"
KeinProfil = KeinProfil & "$nur Montageaufwand, kein Materialpreis$HM28x15, l=100mm$22x175"
KeinProfil = KeinProfil & "$Materialdicke/Umfang$Fl35x25$%"
KeinProfil = KeinProfil & "$"
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Based on this image and the fact that your variable "this" is the concatenation of column B & C, you have a lot of rows where this = ""
Since "i" can be any number between 6 to 5000 For i = 6 To 5000, it would seem to me that the first row (from row 6 on) in which B&C are blank, it would delete all the rows from that row to 5000.
So in the below row 30 is blank for B&C. If that is the first row on which they are blank it would seem that it would delete Rows("30:5000"). Is that not happening ?

VBA Code: 
         Loeschbereich = i & ":5000"
         Rows(Loeschbereich).Delete

1656600211541.png
 
Upvote 0
I am about to go offline. If the above doesn't help. See if you can tell us what it is or isn't doing and provide us with an XL2BB of some sample data.

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
I sorry but I wasnt at home till now. Thats one example about one workbook where I run the code to create the new one with the list. Thats a good example cause:
1. the row 17 is being deleted with the code and that is correct because E18 is empty.
2. the row 21 because of the string E21 otherwise it must also be removed with the code. And that does not happen
matliste macro.xlsm
ABCDEFGH
1
2
3
4 [m][m]
5PosAnzahl der BaugruppenBauteil Stk pro BaugruppeProfilS355=1BreiteLänge
65Walzprofilstützen auf "OKF" oder "Stahl"Art:
71HEA400OKF5,0
82IPE200OKF2,0
93IPE360OKF32,0
104HEB400Stahl4,0
11Stahl
12Stahl
13Zwischensumme
145Kopfbolzendübel einreihigMat.EK
155022x175EUR/Stk
16Zwischensumme
1750fremdverursachte NC-Löcher (durch ZL-Leistung, z.Bsp.: Leitplanken)
182
19Zwischensumme
2050Gebrannte Öffnungen[mm][m]
211Materialdicke/Umfang52
22Zwischensumme
231Gelenkpfetten
24Gelenkpfetten5IPE12015,0
25Gelenkpfetten10HEA40015,0
26Gelenkpfetten12HEB40015,0
27HEA-Pfetten2HEA4002,0
28HEA-Pfetten1HEA40010,0
29HEA-Pfetten
30HEA-Pfetten
31Zwischensumme
Tabelle1


And here is the result running my first macro.
The yellow row should be deleted.
I also wanted to delete with the macro the cells in column D containing a string. But thats another thing :D
Mappe1
ABCDEFGH
1
2
3Materialliste
4GüteBreiteLängeGesamtgewichtGesamtlängen
5ProfilS355=1[m][m][to][m]
622x175EUR/Stk
7Summe0,000,00
8
9HEA400OKF5,00
10Summe0,000,00
11
12HEA40015,00
13Summe0,000,00
14
15HEA4002,00
16HEA40010,00
17Summe0,000,00
18
19HEB400Stahl4,00
20Summe0,000,00
21
22HEB40015,00
23Summe0,000,00
24
25IPE12015,00
26Summe0,000,00
27
28IPE200OKF2,00
29Summe0,000,00
30
31IPE360OKF32,00
32Summe0,000,00
33
34Materialdicke/Umfang52,00
35Summe0,000,00
Tabelle1
Cell Formulas
RangeFormula
F7:G7,F35:G35,F32:G32,F29:G29,F26:G26,F23:G23,F20:G20,F13:G13,F10:G10F7=SUM(F6:F6)
F17:G17F17=SUM(F15:F16)
 
Last edited:
Upvote 0
I am confused. What is the column it is supposed to be checking to delete the row.
The combination of the 2 lines below, mean that is Joining the content of columns B & C and checking whether the Joined value is in your KeinProfil.
Your post above seems to be indicating this is incorrect and that it should just be looking in Column E to see if it is in KeinProfil.

Please clarify

VBA Code: 
   Rows(iRow).Select
   this = UCase(Trim(Selection.Cells(1, 2).Value & Selection.Cells(1, 3).Value))
 
Upvote 0
Alex Blakenburg said:
I am confused. What is the column it is supposed to be checking to delete the row.
The combination of the 2 lines below, mean that is Joining the content of columns B & C and checking whether the Joined value is in your KeinProfil.
Your post above seems to be indicating this is incorrect and that it should just be looking in Column E to see if it is in KeinProfil.

Please clarify

VBA Code: 
   Rows(iRow).Select
   this = UCase(Trim(Selection.Cells(1, 2).Value & Selection.Cells(1, 3).Value))
Click to expand...
If I am not mistaken, and somehow I do. I take column E from matliste macro.xlsm, paste it into column B of the new workbook, and there I check "This" and "KeinProfil" in column B.
Thats the idea.
The case from B34 with the string "Materialdicke/Umfang" should be a case from the KeinProfil to delete that row.

An example with empty cells. When I write something in E18 of matliste macro.xlsm, the macro does not delete this row from the new workbook. That is why I said, that it is the only thing that works.
 
Upvote 0
Can you try using this line:
VBA Code: 
    If InStr(1, KeinProfil, "$" & this & "$", vbTextCompare) > 0 Then
 
Upvote 0
Alex Blakenburg said:
Can you try using this line:
VBA Code: 
    If InStr(1, KeinProfil, "$" & this & "$", vbTextCompare) > 0 Then
Click to expand...
Yes, at least much better. I only have that row 6 which makes no sense because there is nothing to do a sum. And is there any quick way to delete all the cells in column C that contain a string?
Mappe2
ABCDEFGHI
1Bauherr:Angebotsnr.:
2Objekt:Datum:
3Bauort:Bearbeiter:
4GesamtstückzahlGüteBreiteLängeGesamtgewichtGesamtlängenMaterial EK
5G.-StkProfilS355=1[m][m][to][m]EUR/t
6Summe0,000,00
7
85HEA400OKF5,003,1325,001370
9Summe3,1325,00
10
1110HEA40015,006,2550,001370
12Summe6,2550,00
13
142HEA4002,000,504,001370
151HEA40010,001,2510,001370
16Summe1,7514,00
17
1820HEB400Stahl4,0012,4080,001370
19Summe12,4080,00
20
2112HEB40015,009,3060,001370
22Summe9,3060,00
23
245IPE12015,000,2625,000
25Summe0,2625,00
26
2710IPE200OKF2,000,4520,001350
28Summe0,4520,00
29
3015IPE360OKF32,0027,41480,001360
31Summe27,41480,00
32
Tabelle1
Cell Formulas
RangeFormula
F6:G6F6=SUM(F5:F6)
F9:G9,F31:G31,F28:G28,F25:G25,F22:G22,F19:G19,F12:G12F9=SUM(F8:F8)
F16:G16F16=SUM(F14:F15)
 
Upvote 0
TitoElan said:
I only have that row 6 which makes no sense because there is nothing to do a sum.
Click to expand...
In terms of adding the Summe line, I expect your Call statements (x2) are doing that.
I don't fully understand your code but if you replace both Call statement lines with the below that might work for you.
ie don't call the Summe function if it is row 6.
VBA Code: 
         If i <> 6 Then Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)

TitoElan said:
is there any quick way to delete all the cells in column C that contain a string?
Click to expand...
In the context of your code I don't think there is a quick way exactly but since you already looping through every row, it wouldn't be too hard to test for something that is not blank and not numeric and clear it.
eg see If statement below the lines above that is just to show you were to insert the If statement.
VBA Code: 
For i = 6 To 5000
   iRow = i & ":" & i
   Rows(iRow).Select
   If Selection.Cells(1, 3).Value <> "" And Not IsNumeric(Selection.Cells(1, 3).Value) Then Selection.Cells(1, 3).ClearContents
 
Upvote 0
Solution
Alex Blakenburg said:
In terms of adding the Summe line, I expect your Call statements (x2) are doing that.
I don't fully understand your code but if you replace both Call statement lines with the below that might work for you.
ie don't call the Summe function if it is row 6.
VBA Code: 
         If i <> 6 Then Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)


In the context of your code I don't think there is a quick way exactly but since you already looping through every row, it wouldn't be too hard to test for something that is not blank and not numeric and clear it.
eg see If statement below the lines above that is just to show you were to insert the If statement.
VBA Code: 
For i = 6 To 5000
   iRow = i & ":" & i
   Rows(iRow).Select
   If Selection.Cells(1, 3).Value <> "" And Not IsNumeric(Selection.Cells(1, 3).Value) Then Selection.Cells(1, 3).ClearContents
Click to expand...
It worked with that
VBA Code: 
   Else
      If this = "" Then
         Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)
        'If MsgBox("Jetzt wird gelöscht", vbOKCancel) = vbCancel Then Stop
         Loeschbereich = i & ":5000"
         Rows(Loeschbereich).Delete
         i = 5000
      Else
         If this <> last And i <> 6 Then
            Call Zwischensumme(i, GewichtAnfang, LaengeAnfang)
            GewichtAnfang = "F" & i
            LaengeAnfang = "G" & i
           'If MsgBox(last, vbOKCancel) = vbCancel Then Stop
         End If

If I take out both call statements I have to (again :D) close Excel with the task manager. But now its working.
About clearing the cells with strings, it is not working. I will try it more but the main problems are fixed. Thanks a lot!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
316
Peter_SSs
Peter_SSs
Z
  • Solved
VBA to Delete Table Rows with Column A Blank Cell
2
Replies
10
Views
4K
zero269
Z
O
  • Solved
VBA Code to delete Rows with empty rows or spaces will not delete one particular row
2
Replies
13
Views
2K
Alex Blakenburg
A
C
  • Solved
Help adding deletion of rows before sorting?
2
Replies
18
Views
1K
cspengel
C
B
  • Question
If cell in column A is empty insert Text. Stop at row that has no values
Replies
4
Views
614
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,030
Messages
6,128,411
Members
449,449
Latest member
Quiet_Nectarine_

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top