Terima Kasih Pa Hendrik, di file aslinya tidak ada warna, warna disitu hanya
penjelasan saya saja untuk memudahkan penjelasan permasalahan saya, dan datanya
hanya nambah ke bawah aja, tidak ke kanan.
Terima Kasih
Pada Selasa, 22 Oktober 2019 20.40.47 WIB, hendrik karnadi
hendrikkarn...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>
menulis:
Jika datanya banyak (tidak tahu posisi kanan bawahnya) makaRange("A3:E23")
diganti denganRange("A3").CurrentRegion
Salam,HK
----- Pesan yang Diteruskan ----- Dari: hendrik karnadi
<hendrikkarn...@yahoo.com>Kepada: Kamirin Faqoth kamiri...@yahoo.co.id
[belajar-excel] <belajar-ex...@yahoogroups...com>Terkirim: Selasa, 22 Oktober
2019 17.21.55 WIBJudul: Re: [belajar-excel] Mencari Nilai dengan Kriteria
Khusus [1 Attachment]
Dear Kamirin,
Coba copas Macro ini di VBE Sheet (Module1)Sub Rapihkan() 'Mengisi blank
cell dengan sel diatasnya Set Rng = Range("A3:E23") Rng.Select
Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 =
"=R[-1]C" 'Menghapus sel yang interior colornya berearna merah For
Each rg In Rng If rg.Interior.Color = vbRed Then
rg.EntireRow.Delete End If Next rgEnd SubLalu jalankan.
Salam,HK
Pada Selasa, 22 Oktober 2019 16.27.07 GMT+7, Kamirin Faqoth
kamiri...@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com> menulis:
Dear All Member,
Mohon bantuannya, saya agak kesulitan mencari nilai dengan kriteria khusus pada
tabel data yang sifatnya statis, dengan acuan tabel yang saya tandain warna
kuning dan
dengan mengabaikan tabel yang tandain warna merah, Berikut Contoh Potongan File
terlampir, terdiri dari 20 baris, file aslinya lebih dari 1000 baris.
Terim Kasih,Kamirin
#yiv9159249886 #yiv9159249886 -- #yiv9159249886ygrp-mkp {border:1px
solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9159249886
#yiv9159249886ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9159249886
#yiv9159249886ygrp-mkp #yiv9159249886hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9159249886 #yiv9159249886ygrp-mkp #yiv9159249886ads
{margin-bottom:10px;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad
{padding:0 0;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad p
{margin:0;}#yiv9159249886 #yiv9159249886ygrp-mkp .yiv9159249886ad a
{color:#0000ff;text-decoration:none;}#yiv9159249886 #yiv9159249886ygrp-sponsor
#yiv9159249886ygrp-lc {font-family:Arial;}#yiv9159249886
#yiv9159249886ygrp-sponsor #yiv9159249886ygrp-lc #yiv9159249886hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9159249886
#yiv9159249886ygrp-sponsor #yiv9159249886ygrp-lc .yiv9159249886ad
{margin-bottom:10px;padding:0 0;}#yiv9159249886 #yiv9159249886actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9159249886
#yiv9159249886activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9159249886
#yiv9159249886activity span {font-weight:700;}#yiv9159249886
#yiv9159249886activity span:first-child
{text-transform:uppercase;}#yiv9159249886 #yiv9159249886activity span a
{color:#5085b6;text-decoration:none;}#yiv9159249886 #yiv9159249886activity span
span {color:#ff7900;}#yiv9159249886 #yiv9159249886activity span
.yiv9159249886underline {text-decoration:underline;}#yiv9159249886
.yiv9159249886attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9159249886 .yiv9159249886attach div a
{text-decoration:none;}#yiv9159249886 .yiv9159249886attach img
{border:none;padding-right:5px;}#yiv9159249886 .yiv9159249886attach label
{display:block;margin-bottom:5px;}#yiv9159249886 .yiv9159249886attach label a
{text-decoration:none;}#yiv9159249886 blockquote {margin:0 0 0
4px;}#yiv9159249886 .yiv9159249886bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9159249886
.yiv9159249886bold a {text-decoration:none;}#yiv9159249886 dd.yiv9159249886last
p a {font-family:Verdana;font-weight:700;}#yiv9159249886 dd.yiv9159249886last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9159249886
dd.yiv9159249886last p span.yiv9159249886yshortcuts
{margin-right:0;}#yiv9159249886 div.yiv9159249886attach-table div div a
{text-decoration:none;}#yiv9159249886 div.yiv9159249886attach-table
{width:400px;}#yiv9159249886 div.yiv9159249886file-title a, #yiv9159249886
div.yiv9159249886file-title a:active, #yiv9159249886
div.yiv9159249886file-title a:hover, #yiv9159249886 div.yiv9159249886file-title
a:visited {text-decoration:none;}#yiv9159249886 div.yiv9159249886photo-title a,
#yiv9159249886 div.yiv9159249886photo-title a:active, #yiv9159249886
div.yiv9159249886photo-title a:hover, #yiv9159249886
div.yiv9159249886photo-title a:visited {text-decoration:none;}#yiv9159249886
div#yiv9159249886ygrp-mlmsg #yiv9159249886ygrp-msg p a
span.yiv9159249886yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9159249886
.yiv9159249886green {color:#628c2a;}#yiv9159249886 .yiv9159249886MsoNormal
{margin:0 0 0 0;}#yiv9159249886 o {font-size:0;}#yiv9159249886
#yiv9159249886photos div {float:left;width:72px;}#yiv9159249886
#yiv9159249886photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv9159249886
#yiv9159249886photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9159249886
#yiv9159249886reco-category {font-size:77%;}#yiv9159249886
#yiv9159249886reco-desc {font-size:77%;}#yiv9159249886 .yiv9159249886replbq
{margin:4px;}#yiv9159249886 #yiv9159249886ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9159249886 #yiv9159249886ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9159249886
#yiv9159249886ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9159249886
#yiv9159249886ygrp-mlmsg select, #yiv9159249886 input, #yiv9159249886 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv9159249886
#yiv9159249886ygrp-mlmsg pre, #yiv9159249886 code {font:115%
monospace;}#yiv9159249886 #yiv9159249886ygrp-mlmsg *
{line-height:1.22em;}#yiv9159249886 #yiv9159249886ygrp-mlmsg #yiv9159249886logo
{padding-bottom:10px;}#yiv9159249886 #yiv9159249886ygrp-msg p a
{font-family:Verdana;}#yiv9159249886 #yiv9159249886ygrp-msg
p#yiv9159249886attach-count span {color:#1E66AE;font-weight:700;}#yiv9159249886
#yiv9159249886ygrp-reco #yiv9159249886reco-head
{color:#ff7900;font-weight:700;}#yiv9159249886 #yiv9159249886ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv9159249886 #yiv9159249886ygrp-sponsor
#yiv9159249886ov li a {font-size:130%;text-decoration:none;}#yiv9159249886
#yiv9159249886ygrp-sponsor #yiv9159249886ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv9159249886
#yiv9159249886ygrp-sponsor #yiv9159249886ov ul {margin:0;padding:0 0 0
8px;}#yiv9159249886 #yiv9159249886ygrp-text
{font-family:Georgia;}#yiv9159249886 #yiv9159249886ygrp-text p {margin:0 0 1em
0;}#yiv9159249886 #yiv9159249886ygrp-text tt {font-size:120%;}#yiv9159249886
#yiv9159249886ygrp-vital ul li:last-child {border-right:none
!important;}#yiv9159249886
