[[20170807115436]] 『最終列の取得』(T17) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『最終列の取得』(T17)

「Cells(3, Columns.Count).End(xlToLeft).Column」で3行目の最終列を
取得してデータ処理をしていましたが、最近元データの仕様が変わった?
らしく不具合が発生しています。

 不具合内容)
 O列	P	Q	R	S	T
 20	20	15	13	8	6
上記は、元データからシートにコピペしたO列3行目の一例を示します。
求める最終列は「20」ですが、マクロ実行後「33」が返ってきます。

調べると、U列からAG列のセルに「␣(スペース)」が2個入っています。
原因はこれかと推察しますが、対処方法がわかりません。
応急的にはスペースを都度「削除」しましたが、元データはネットから
勝手にコピペしているため、スペース位置の特定はできません。

スペースを無視して最終列を取得するコードをアドバイス願います。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


For i = Cells(3, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Replace(StrConv(Cells(3, i).Value, vbNarrow), " ", "") <> "" Then MsgBox "最終行は" & i & "行": Exit For
Next i
(mm) 2017/08/07(月) 12:40

修正
For i = Cells(3, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Replace(StrConv(Cells(3, i).Value, vbNarrow), " ", "") <> "" Then MsgBox "最終列は" & i & "列": Exit For
Next i

(mm) 2017/08/07(月) 12:57


mm様、 早々にありがとうございます。

StrConv関数は知らなかったです。
試行結果では「最終列は33列」と出ますが、ひょっとして件のセル内容は
スペース(␣)ではないのでしょうか?

U3セル選択状態でF2キーを押すと、「|」点滅が左端から2スペース分の位置に
出ているので、スペースと思っているのですが...
“正体”がわかる確認方法あれば教えてください。
(T17) 2017/08/07(月) 13:12


 activesheet.usedrange.replace chr(160), "", 2
 でどうですか?
 スマホなので...
( seiya) 2017/08/07(月) 13:18

MsgBox "U3の文字コードは" & Asc(Range("U3").Value)

(mm) 2017/08/07(月) 13:35


 修正
 chr(160) は chrw(160)
(seiya) 2017/08/07(月) 14:04

seiya様、 ありがとうございます。

mm様コードの前にご提示コードを挿入した結果「33」で変化なしでした。

で、その後のmm様確認用コードの結果が「文字コード63」と出ましたので
seiya様コードの「chr(160)」をchr(63)にして実行すると...、なんと
全部のデータが“消えましたっ”???

消えたのは「=CHAR(63)」は「?」だったので納得ですが、「=CODE(RIGHT(U3,1))」で
確認すると「160」なんですよねぇ...?

と、ここまで書いてアップしようとしたらseiya様の修正コードが...、
「20」で解決できましたっ!

今の状況を貧弱な脳みそにもわかるように説明願えないでしょうか?
(T17) 2017/08/07(月) 14:26


 スマホからなので...
 下記リンクを参照して下さい。
https://support.microsoft.com/ja-jp/help/404928
(seiya) 2017/08/07(月) 14:47

mm様、seiya様ありがとうございます。

ご紹介いただいたサイトを含めいろいろ調べてみましたが、
ネットからコピペする場合にはあり得るケースのようですね。

元データが使用している文字コード標準?とのミスマッチが
原因..との理解でいいのでしょうか?
であれば、今回はうまくいったけど、別のケースもあり得ると。

都度対応..しかないのですかねぇ、いい勉強になりました。
(機会あればもう少し突っ込んで調べたいと思います)

にしても、いつもながらMSのサイトはわかりづらいですねぇ〜。
もう少し文章力のある翻訳者?はいないのかしら..
(自分のオツムのことは脇に“置いて”いますが)

これからもよろしくお願いいたします。
(T17) 2017/08/08(火) 10:22


ども^^

見えない文字があるなら、ワークシートで使うclean関数で除去する方法もあると思います。
(もしかしたら消えない文字もあったかも)
同時にスペースを消すならTrim関数も使うといいと思います。
(ワークシートのTrim関数とVBAのTrim関数とで仕様が違います。調べてみて仕様に合う方を^^)
他には、エクセルの置換機能や区切り位置の機能でも除去できそうですね。

あと、別のアプローチとしては、
検索機能(Findメソッド)で最後の列を検索してしまうのもありかも知れません。

他には、「聞く」だけでなく、自分で技(どうやったら最後の列を正しく判別できるか)を
編み出してもいいかと思います^^

(まっつわん) 2017/08/09(水) 16:37


まっつわん様、 ありがとうございます。

clean関数は知りませんでしたが、trim関数同様に今回のケースでは
うまくいきませんでした。
ユニコード「char(160)」とわかっていれば、findやsubstituteと
組合せて除去(置換)できるようですが...

今回のように「見た目は空白だが、“何かいる ^ ^;”」場合に、他に
どのようなケースがあるのか..仕事そっちのけで探してますが、
ズバリのサイトに辿り着けてません T T。
(T17) 2017/08/10(木) 09:56


「見た目は空白..」は「見た目は空欄..」というべきですかね
(T17) 2017/08/10(木) 10:01

http://microsoft.public.jp.excel.narkive.com/fspTLqkX

最初に「テキスト形式で貼り付け」るのが最も簡単な解決方法だったりしますが。 ↑ぼくはなぜかこの方法でいつも貼り付けてますね。。。

>ユニコード「char(160)」とわかっていれば、findやsubstituteと
>組合せて除去(置換)できるようですが...
解ってなくても可能性があるなら無条件で置換機能で置き換えてはいかがでしょう?
対象が無くてエラーになることはないと思いいますが。。。
(まっつわん) 2017/08/10(木) 18:09


それから、
検索機能で
キーワードを「*」で逆回して検索して、最終列は取れませんでしたか?
だめかも知れないですけど。。。

(まっつわん) 2017/08/10(木) 18:11


 >今回のように「見た目は空白だが、“何かいる ^ ^;”」場合に、他に 
 >どのようなケースがあるのか..仕事そっちのけで探してますが、 
 >ズバリのサイトに辿り着けてません T T。

 昔、同じような話があって、調べたことがあります。
     ↓
[[20120920205425]] 『文字の後部にある空白を削除したい』(かき氷)

 その時の私のレスを見ると、以下の4つが危なそうだった
               
 >160, 8194, 8195, 8201    'これ以外にあるのかどうか分かりません。

(半平太) 2017/08/10(木) 22:33


 たぶん、Tabの一種じゃないかと。
 昔、なんだか解らなくて、半角スペースがなくなるまで、ループ組んでしつこく消したけど、
 なんかのTabみたいだった。

(BJ) 2017/08/11(金) 03:46


皆さま、 ありがとうございます。レス遅くなり申訳ありません。

まっつわん様
 >「テキスト形式で貼り付け」るのが最も簡単な解決方法...
→そうしたいのですが、「値貼付け」を選択すると全てが1セルに
貼付けられて(表形式にならない)しまいます。
これは後述の「Html文書」だからでしょうかね?
(Html文書のことは詳しく知りませんが)

 >キーワードを「*」で逆回して検索して、最終列は取れませんでしたか?
→具体的な方法がわからず..すみません、 お時間ください。−☆

半平太様
今回のケースはmm様、seiya様とのやりとりで一応の解決をみましたが、
上述の☆がうまくいかない場合は「8194, 8195, 8201」も組入れようと思います。

BJ様
そういえばネット徘徊中にTabの話をみかけましたが、これも半平太様のいわれる
Html文書からのコピーにまつわるものなんでしょうかね。
調べてみようと思います。
(T17) 2017/08/13(日) 17:45


まっつわん様  >「テキスト形式で貼り付け」るのが最も簡単な解決方法... →そうしたいのですが、「値貼付け」を選択すると全てが1セルに 貼付けられて(表形式にならない)しまいます。 これは後述の「Html文書」だからでしょうかね? (Html文書のことは詳しく知りませんが)

んと、、、できれば実験してみたいのですが、
具体的なサイトがないですかね?

Webサイトから表を取り込むならWebクエリという機能もありますが、、、、
(まっつわん) 2017/08/14(月) 06:41


まっつわん様、 ありがとうございます。

件のサイトは会員サイトになっており、公開はちょっと..お許しください。

Webクエリ..知りませんでしたので、試してみました。
で、結果は同じように“一見空欄なのに何か”がいました ^ ^:。

興味深いのは、Webクエリからのデータはseiya様コードでは削除できないこと。
(オプションの書式設定を弄ってみましたが同じでした)
それもそのはずで、Webクエリの“何か”は=code()で調べると「32」でした。
(デフォルト設定で)

これ以上は二日酔いの脳ミソでは(なくても?)シンドそうなので、
一旦リセットして今から飲み直します。 否、外出します。

(T17) 2017/08/14(月) 10:27


 >できれば実験してみたいのですが、 

 テスト用のhtmlを書きました。

 「@」を「&」マークに変更してから(※1)、
  拡張子を「html」にして保存後、オープンしてください。

 (※1)初めから「&」にしてアップすると、掲示板では消えてしまうので・・

  <HTML> 
   <HEAD>
   <META http-equiv="Content-Type" content="text/html; charset=Shift_JIS">
   <TITLE>TEST</TITLE>
   </HEAD>
   <BODY>
   <TABLE border="1">
     <TBODY>
       <TR>
         <TD>Item</TD>
         <TD>Item1</TD>
         <TD>Item2</TD>
         <TD>Item3</TD>
         <TD>Item4</TD>
         <TD>Item5</TD>
       </TR>
       <TR>
         <TD>空白に見える</TD>
         <TD>みかん</TD>
         <TD>@nbsp;</TD>
         <TD>@ensp;</TD>
         <TD>@emsp;</TD>
         <TD>@thinsp;</TD>
       </TR>
     </TBODY>
   </TABLE>
   </BODY>
  </HTML>

(半平太) 2017/08/14(月) 11:55


半平太様、 ありがとうございます。

文中にある「<HEAD>」以下をエクセルシートにコピペして、
ご指示通り置換〜「html」で保存すればいいのでしょうか?

保存したのをクリックするとWebページが開きました。
中身は文中の「@」を「&」に置換(4か所)したものですが、
この状態でよろしいのでしょうか?

意図がわからず..すみません。

(T17) 2017/08/16(水) 08:42


おぉぉ?エクセルにコピペでいいのかな。。。。
ワードかメモ帳かなにかかと。。。^^;
まだ、「htmlで保存」が出来てません^^;

T17さん>>
僕用にテストデータ作ってくれただけかと。。。^^;

どこか、現象が再現できるサイトがあればいいのですけどねぇ。。。

(まっつわん) 2017/08/16(水) 09:02


いやぁ〜お恥ずかしい ^ ^;

テキストにしたら表が表示されました。
で、「Item3,4,5」は何れも=code() では「63」でした。
(当然、seiya様コードでは削除できませんでした)

(T17) 2017/08/16(水) 09:23


 舌足らずで済みません。

 htmlはテキストファイルです。(メモ帳か、テキストエディタでやってください)

(半平太) 2017/08/16(水) 10:19


 > =code() では「63」でした。
 こんな感じでできませんか?

    With ActiveSheet.UsedRange
        .Value = .Parent.Evaluate("substitute(" & .Address & ",char(63),"""")")
    End With
(seiya) 2017/08/16(水) 11:14

 私のhtmlは、まっつわんさんの『できれば実験してみたいのですが、』に対するテスト用です。

 T17さんの現在の問題を拡大させる意図はありません。

 ちょっと上の方で との事だったので・・ちょっと処理が厄介な空白のサンプルの積りです。
            ↓
 >件のサイトは会員サイトになっており、公開はちょっと..お許しください。

(半平太) 2017/08/16(水) 12:14


seiya様、半平太様、 ありがとうございます。

seiya様コードで半平太様Web表で試したところ、全てが「Item」になりました。

 半平太様 Web頁					
 Item	        Item1	Item2	Item3	Item4	Item5 
 空白に見える	みかん		 	 	 
↓					
 seiya様コード実行後					
 Item	Item	Item	Item	Item	Item
 Item	Item	Item	Item	Item	Item

私のオツムは???状態で、せっかくのご回答を忠実に試せているのかすこぶる不安です。
現時点の私の理解はこうです...
・Webから表形式のデータをそのままエクセルにコピペすると“一見空欄に見える”セルには
“何か”がいる場合があるらしい
・どうやらそれは「空白(スペース)」や「Tab」の表記の仕方?が、データ元と自分のPCの
文字コードのミスマッチ?によるらしい

現在、先のseiya様コード(2017/08/07(月) 14:04)でうまく対処できていますので、ここらで
一旦はクローズすべきかと思いますが、もし下記に関して有益なヒントあれば、よろしくお願いいたします。

・そもそもどういったケースで件の現象が起きるのか、全てわかることは可能なのか?
(わかれば、自ずとあらゆるケースでの「最終列取得」が可能と思いますので)

(T17) 2017/08/16(水) 23:42


 おっと...
 これで試してください。

    With ActiveSheet.UsedRange
        .Value = .Parent.Evaluate("index(substitute(" & .Address & ",char(63),""""),)")
    End With
(seiya) 2017/08/16(水) 23:48

seiya様、 取急ぎ試行結果を報告します。

今回のは半平太様の表に見た目の変化はありませんでしたが、
「Item3-5」は「63」で空白除去できていません...

(T17) 2017/08/17(木) 09:03


 そうですか...
 昔作成したのですが

 Sub test()
     Dim a, i As Long, ii As Long
     With ActiveSheet.UsedRange
         a = .Value
         For i = 1 To UBound(a, 1)
             For ii = 1 To UBound(a, 2)
                 a(i, ii) = CleanAll(a(i, ii))
         Next ii, i
         .Value = a
     End With
 End Sub

 Function CleanAll(ByVal txt As String) As String
     Static RegX As Object
     If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
     With RegX
         .Global = True
         .Pattern = "[\f\n\r\t\v\u00A0]"
         CleanAll = .Replace(txt, "")
     End With
 End Function

(seiya) 2017/08/17(木) 10:01


seiya様

結果は code()=63 で変わらずでした。
(テスト用の表はセルA1以降にコピペしています)

尚、今回のはシート内に上記表以外の使用セルがあると「a(i, ii) = CleanAll(a(i, ii))」のところで、
エラー13(型不一致)が出ます。

これはご想定内..でしょうか?

(T17) 2017/08/17(木) 10:57


今回のはシート内に上記表以外の使用セルがあると「a(i, ii) = CleanAll(a(i, ii))」のところで、 エラー13(型不一致)が出ます。

 UsedRangeなので、それは考えられませんが?
 シート内に値が単一セルのみ、またはエラー値があるとエラーになると思いますけど。

 >code()=63 で変わらずでした。

         .Pattern = "[\f\n\r\t\v\" & chr(63) & "]"

 に変更して見てください。
 (手元にデータがないのでテストのしようがない...)

(seiya) 2017/08/17(木) 11:24


エラーの件は承知いたしました。
シート内に確かにエラー値が含まれていました。

変更コードの結果ですが、「63」で変わりなしでした。
因みにVBAで調べると「Asc()=63」ですが「AscW()=8201」でした。
(半平太様が書かれていたものですね)
(T17) 2017/08/17(木) 11:44


 >AscW()=8201
 が判明すれば

 activesheet.usedrange.replace chrw(8201), "", 2

 でできませんか?
(seiya) 2017/08/17(木) 11:55

seiya様〜ご回答いただいた皆さま
コメントありがとうございます。
また随所に舌足らずな応答ですみませんでした。

先に書きましたように、[2017/08/07(月) 14:04]のコードで現在は
対処できていますし、test表でも対応できることを確認済みです。
(因みにtest表のItem3,4,5はAscW()で8194,8195,8201でした)

よって、本質問は一旦クローズさせていただきます。

今の関心事は、不都合が生じる度にコード変更しなくて済むように、
予め今回のような事例を“全て”盛り込んだコードにできないか..
というものですが、これについてはオツムの回転が悪く未だ学習中であり、
ポイントが絞れたら改めて質問をさせていただきたいと思います。

これからもよろしくお願いいたします。
(T17) 2017/08/17(木) 13:28


 >今の関心事は、不都合が生じる度にコード変更しなくて済むように、 
予め今回のような事例を“全て”盛り込んだコードにできないか..

 該当コードをよく調べてループ

 Dim e
 For Each e In Array(160, 8194, 8195, 8201)
     Cells.Replace ChrW(e), "",2
 Next
(seiya) 2017/08/17(木) 14:05

seiya様、 早速ありがとうございます。

本欄で挙がった4つで“全て”と言い切れるといいのですが...
(杞憂であれば、嬉しいですが)
あるいは、code()=63に該当するものが“全て”ならそれを元に対応できると
勝手に思っているのですが、調べていてもそれに言及しているサイトを
探せなくて...

ともあれ、ご提示いただいたコードは先の“発展版”として採用させて
いただきます。
(T17) 2017/08/17(木) 15:06


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.