macro for deleting cells trought column A

T

tabaðija

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.
 
M

Mike H

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
 
T

tabađija

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


before:

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
 
R

Rick Rothstein

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?
 
T

tabaðija

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?
Click to expand...

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
 
R

Rick Rothstein

See inline comments...
I dont understand what need to change in

Const DataStartRow As Long = 3 -> this dont understand
Click to expand...

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
Click to expand...

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?
 
T

tabaðija

See inline comments...



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.
Click to expand...

i understand that Const DataStartRow As Long = 3 start at third place
of data but did not work
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?
Click to expand...

hehe

....the one country it's enouhg. i want to get rid all off names and
leave one and
in same worksheet (column C) i want to SUM off all

like this i presume that need two loop (or macros), one for SUM and
one to rid
of unnecessary Country names

TNX for effort.

i will do something about this tomorrow at work :)
 
Top