For completeness I see I forgot to declare the variable 'sname'
Dim sName As String
Regards,
Peter T
PS glad you got it working
"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> The example code is not looking for a "special name", it simply returns
the
> name of A1 if it has a name, otherwise the empty string confirms A1 is not
a
> named cell.
>
> If I understand your original correctly you want to delete entire columns
of
> header cells that are not named. Following should delete all columns
between
> B1:J1 (header range) if the cell in row 1 is not a named cell.
>
> Sub test3()
> Dim col As Long
> Dim rHeaders As Range
>
> Set rHeaders = ActiveSheet.Range("B1:J1")
> For col = rHeaders.Count To 1 Step -1
> On Error Resume Next
> sname = ""
> sname = rHeaders(1, col).Name.Name
> On Error GoTo 0
>
> If sname = "" Then
> ActiveSheet.Columns(rHeaders(1, col).Column).Delete
> End If
> Next
> End Sub
>
> Use with caution, might accidently delete data if the header cell is not
> named.
>
> Regards,
> Peter T
>
>
>
> "Mirja" <(E-Mail Removed)> wrote in message
> news:1DE5612F-E7EC-44F3-93AA-(E-Mail Removed)...
> > Hi Peter,
> > thanks for replying. Unfortunately, this did not work. The command is
for
> a
> > special name, isn't it? But I like to check all cells of the first row
for
> > ANY name. So if a cell has any name, then don't delete the column, if a
> cell
> > is not named, delete the column ... Do you know how to do that?
> > Thanks again!
> > Mirja
> >
> > "Peter T" wrote:
> >
> > > Hi Mirja,
> > >
> > > Sub test()
> > > On Error Resume Next
> > > sName = ""
> > > sName = Range("A1").Name.Name
> > > On Error GoTo 0
> > > If sRangeName = "" Then
> > > MsgBox "not named"
> > > Else
> > > MsgBox sName
> > > End If
> > > End Sub
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Mirja" <(E-Mail Removed)> wrote in message
> > > news:BAD3F6EE-D5A4-4A2F-8653-(E-Mail Removed)...
> > > > I would like to do the following within VBA:
> > > > - Select range from first cell to last cell in first row of a
> database.
> > > > - Find arguments in this range (e.g. if cell value = "Sales" then
name
> the
> > > > cell = "Sales"). I have several arguments.
> > > > - Afterwards, I want to delete all columns that do not contain these
> > > > arguments (in the example above: If the first cell of a column is
not
> > > > "Sales", then delete the column).
> > > >
> > > > My main problem: How do I check if a cell is named or not?
> > > >
> > > > Thank you in advance!
> > > > Mirja
> > > >
> > >
> > >
> > >
>
>
Similar Threads
