| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for deleting cells trought column A
hello!
my problem begins with a lot of data. i need macro to delete all number values in A columns and a entire row. or all cell which begins with a certain number (1,...,9). tnx. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
Hi,
You weren't specific about what the number was to begin with so this uses 9. Change to suit. Right click your sheet tab, view code and paste this in and run it. Sub delete_Me() Dim CopyRange As Range, MyRange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each C In MyRange If Left(C.Value, 1) = 9 Then If CopyRange Is Nothing Then Set CopyRange = C.EntireRow Else Set CopyRange = Union(CopyRange, C.EntireRow) End If End If Next If Not CopyRange Is Nothing Then CopyRange.Delete End If End Sub Mike "tabađija" wrote: hello! my problem begins with a lot of data. i need macro to delete all number values in A columns and a entire row. or all cell which begins with a certain number (1,...,9). tnx. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
tnx MAN...U RESCUE ME...my fingers almost palayse.
do u have idea how to make a SUM(cell A) by the same Atribut's and then delete all but one atribut and leave the SUM befo ALBANIJA 211 AUSTRIJA 4260 AUSTRIJA 28274 AUSTRIJA 12696 AUSTRIJA 1199671 AUSTRIJA 12870 AUSTRIJA 13926 AUSTRIJA 406254 AUSTRIJA 249971 AUSTRIJA 89479 AUSTRIJA 106262 AUSTRIJA 2020476 BELGIJA 56070 BELGIJA 543 BELGIJA 35401 BELGIJA 202120 BOSNA I HERCEGOVINA 31718 BOSNA I HERCEGOVINA 26061 BOSNA I HERCEGOVINA 58798 BOSNA I HERCEGOVINA 54642 BOSNA I HERCEGOVINA 28931 BOSNA I HERCEGOVINA 2781 BOSNA I HERCEGOVINA 868 BOSNA I HERCEGOVINA 7422 BOSNA I HERCEGOVINA 9658 BOSNA I HERCEGOVINA 16383 BOSNA I HERCEGOVINA 152 BOSNA I HERCEGOVINA 172 BOSNA I HERCEGOVINA 2299 BOSNA I HERCEGOVINA 6775 BOSNA I HERCEGOVINA 17237 BOSNA I HERCEGOVINA 945 BOSNA I HERCEGOVINA 2248 BOSNA I HERCEGOVINA 1174 BOSNA I HERCEGOVINA 19470 after: ALBANIJA 211 AUSTRIJA 4260 28274 12696 1199671 12870 13926 406254 249971 89479 106262 2020476 BELGIJA 56070 543 35401 202120 BOSNA I HERCEGOVINA 31718 26061 58798 54642 28931 2781 868 7422 9658 16383 152 172 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
Producing the list with deleted duplicate country names can be done with
this macro (change the two Const statements and the single With statement to reflect your actual data set up)... Sub DeleteDuplicateCountryNames() Dim C As Range Dim X As Long Dim LastRow As Long Const DataStartRow As Long = 3 Const DataColumn As String = "A" With Worksheets("Sheet6") LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row For X = LastRow To DataStartRow - 1 Step -1 Set C = .Cells(X, DataColumn) If InStr(C.Offset(-1).Value, Left(C.Value, _ InStrRev(C.Value, " "))) = 1 Then C.Value = Mid(C.Value, InStrRev(C.Value, " ") + 1) End If Next End With End Sub I would add the summation you want to the macro, but you didn't make clear what you wanted summed up (the individual sums per country or the sum for all the countries in the list). Which did you want? Also, where did you want this or these sums placed at? -- Rick (MVP - Excel) "tabadija" wrote in message ... tnx MAN...U RESCUE ME...my fingers almost palayse. do u have idea how to make a SUM(cell A) by the same Atribut's and then delete all but one atribut and leave the SUM befo ALBANIJA 211 AUSTRIJA 4260 AUSTRIJA 28274 AUSTRIJA 12696 AUSTRIJA 1199671 AUSTRIJA 12870 AUSTRIJA 13926 AUSTRIJA 406254 AUSTRIJA 249971 AUSTRIJA 89479 AUSTRIJA 106262 AUSTRIJA 2020476 BELGIJA 56070 BELGIJA 543 BELGIJA 35401 BELGIJA 202120 BOSNA I HERCEGOVINA 31718 BOSNA I HERCEGOVINA 26061 BOSNA I HERCEGOVINA 58798 BOSNA I HERCEGOVINA 54642 BOSNA I HERCEGOVINA 28931 BOSNA I HERCEGOVINA 2781 BOSNA I HERCEGOVINA 868 BOSNA I HERCEGOVINA 7422 BOSNA I HERCEGOVINA 9658 BOSNA I HERCEGOVINA 16383 BOSNA I HERCEGOVINA 152 BOSNA I HERCEGOVINA 172 BOSNA I HERCEGOVINA 2299 BOSNA I HERCEGOVINA 6775 BOSNA I HERCEGOVINA 17237 BOSNA I HERCEGOVINA 945 BOSNA I HERCEGOVINA 2248 BOSNA I HERCEGOVINA 1174 BOSNA I HERCEGOVINA 19470 after: ALBANIJA 211 AUSTRIJA 4260 28274 12696 1199671 12870 13926 406254 249971 89479 106262 2020476 BELGIJA 56070 543 35401 202120 BOSNA I HERCEGOVINA 31718 26061 58798 54642 28931 2781 868 7422 9658 16383 152 172 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
On Jan 28, 4:46*pm, "Rick Rothstein"
wrote: Producing the list with deleted duplicate country names can be done with thismacro(change the two Const statements and the single With statement to reflect your actual data set up)... Sub DeleteDuplicateCountryNames() * Dim C As Range * Dim X As Long * Dim LastRow As Long * Const DataStartRow As Long = 3 * Const DataColumn As String = "A" * With Worksheets("Sheet6") * * LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row * * For X = LastRow To DataStartRow - 1 Step -1 * * * Set C = .Cells(X, DataColumn) * * * If InStr(C.Offset(-1).Value, Left(C.Value, _ * * * * * * * * * InStrRev(C.Value, " "))) = 1 Then * * * * C.Value = Mid(C.Value, InStrRev(C.Value, " ") + 1) * * * End If * * Next * End With End Sub I would add the summation you want to the macro, but you didn't make clear what you wanted summed up (the individual sums per country or the sum for all the countries in the list). Which did you want? Also, where did you want this or these sums placed at? I dont understand what need to change in Const DataStartRow As Long = 3 - this dont understand Const DataColumn As String = "A" - this understand With Worksheets("Sheet6") -this understand(place name of my Sheet) BTW tnx very much i need individual sums per country and then delete unwanted country's. need only summation and one country |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
See inline comments...
I dont understand what need to change in Const DataStartRow As Long = 3 - this dont understand Put the row number of your first piece of data in the DataStartRow constant. For your posted example data, that would be the row number where "ALBANIJA 211" is located. I don't know your layout (do you have header rows for example... if so, do you have a blank separating row between your header and first piece of data?)... so you have to tell the code where your data is. i need individual sums per country and then delete unwanted country's. need only summation and one country But where do you want these sums to go? In an array in memory? On the worksheet somewhere? If on the worksheet, where (what column and/or row)? And what do you mean by "delete unwanted country's"? What makes a country wanted or unwanted? -- Rick (MVP - Excel) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro for deleting cells trought column A
On Jan 28, 9:04*pm, "Rick Rothstein"
wrote: See inline comments... I dont understand what need to change in * Const DataStartRow As Long = 3 * * *- this dont understand Put the row number of your first piece of data in the DataStartRow constant. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching Column and deleting cells -VBA | Excel Programming | |||
| Deleting Duplicate Cells in one Column. | Excel Discussion (Misc queries) | |||
| Deleting blank cells in a column | Excel Programming | |||
| How to quit from Powerpoint launched trought VBA codes in Excel | Excel Programming | |||
| Deleting blank cells in a column | Excel Discussion (Misc queries) | |||
Linear Mode
